Data Import & Export

How to Create Repeated Store and UPC Combinations in Excel

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to generate a comprehensive list in Excel that contains every possible combination of items from a store list and a UPC list, repeating each store for every available UPC.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating a Cartesian product from two separate lists (Stores and UPCs) for inventory tracking, system imports, or comprehensive data reporting.
Observed behavior: The goal state is to have a new table that automatically pairs every single store with every single UPC without having to manually copy and paste the rows repeatedly.

Before you start

Ensure your list of stores and list of UPCs are organized into two separate, distinct columns without any blank rows. If you plan to use dynamic array formulas, verify that you are running Excel 365 or a compatible version that supports functions like REDUCE and VSTACK.

Solution 1Recommended

Use a Dynamic Array Formula (Excel 365)

This is the most efficient method for Microsoft 365 users, as it dynamically generates all possible combinations instantly using a single formula.

By utilizing modern array functions, you can manipulate and combine the arrays directly in the spreadsheet. This creates a Cartesian product that updates automatically if you modify the original data.

Define your data ranges

Identify the exact cell ranges for your stores (e.g., A2:A101) and your UPCs (e.g., C2:C4). Make sure these ranges are accurate to avoid missing data.

Input the dynamic formula

Select an empty cell where you want the new combined list to start (for example, cell H2). Enter the following formula: =DROP(REDUCE("",TOCOL(A2:A101&"|"&TRANSPOSE(C2:C4)),LAMBDA(a,i,VSTACK(a,TEXTSPLIT(i,"|")))),1).

Apply and review

Press Enter. The formula will automatically spill down and across, generating two columns representing every combination of your stores and UPCs.

Dynamic Updates: Because this relies on formulas, any changes you make to the original store or UPC names will instantly reflect in your combined list.

Solution 2

Create a Cartesian Product using Power Query

Power Query is the ideal solution for large datasets or for users on older versions of Excel that do not support dynamic array formulas.

Solution 3

Use SEQUENCE and INDEX Formulas

A mathematical formula approach suitable for scenarios where you need to calculate repetitions based on a set number of items.

WPS Spreadsheet Solution

Generate Data Combinations Seamlessly in WPS Office

WPS Spreadsheet provides powerful data processing capabilities, including full support for modern array formulas and seamless compatibility with Microsoft Excel files. You can efficiently manage and cross-reference large datasets like Store and UPC combinations using WPS.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your Store and UPC lists.
2. Enter the formula: Select an empty target cell and input the combination formula to merge your arrays.
3. Process the combinations: Press Enter to spill the array results, instantly giving you a complete Cartesian product of your data.

100% format compatibility with Microsoft Excel (.xlsx and .xls) filesRobust support for advanced functions and dynamic array formulasLightweight architecture for faster processing of large data setsIntuitive, tabbed interface familiar to Office users

microsoft office alternative - wps office

Frequently Asked Questions

What does a #SPILL! error mean when using dynamic arrays?

A #SPILL! error occurs when the dynamic array formula attempts to populate multiple cells with your combinations, but one or more of the required destination cells already contain data. To fix it, simply clear the cells directly below and to the right of your formula.

Can I combine more than two lists using these methods?

Yes, but the Excel formulas become significantly more complex. If you need to combine three or more lists (like Stores, UPCs, and Dates), Power Query is the most reliable method. You can repeatedly add custom columns for each new table and expand them successively.

Why is the Power Query option better for huge datasets?

Array formulas calculate in real-time, which can slow down your workbook if you are generating tens of thousands of combinations. Power Query processes the Cartesian product in the background and loads a static result, significantly improving spreadsheet performance.

Excel & Data Problems