How to Create Repeated Combinations from Two Lists in Excel
Question details
The user needs to generate all possible combinations (a Cartesian product) from two separate lists in Excel while preserving specific number formats such as leading zeros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining a primary list of sequential values (e.g., 001 to 365) with a secondary list of categories (e.g., 01 to 10) to generate a comprehensive matched dataset.
- Observed behavior
- Producing a single column where every item in the first list is systematically paired with every item in the second list, maintaining exact text formatting.
Ensure both of your lists are organized in separate columns without blank cells, and identify the cell range for each list before writing the formula.
Use the TOCOL and TRANSPOSE Dynamic Array Formula
For users on modern versions of Excel, combining dynamic array functions is the fastest and most efficient way to generate repeated combinations.
This approach uses the TEXT function to preserve specific formatting (like leading zeros), while TRANSPOSE aligns the data to create a two-dimensional array of combinations. Finally, TOCOL flattens the result into a single column.
Click on the empty cell where you want the new combined list to start. Ensure there is plenty of empty space below it.
Type the formula: =TOCOL(TEXT(A1:A365,"000")&TRANSPOSE(TEXT(C1:C10,"00"))). Adjust the ranges A1:A365 and C1:C10 to match your actual data.
Press Enter. The formula will automatically spill the results down the column, pairing every item in the first list with every item in the second.
Create Combinations Using a VBA Macro
If you are using an older version of Excel that lacks the TOCOL function, a VBA macro can reliably automate the cross-join process.
Combine Lists Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas and VBA macros, allowing you to quickly combine lists and process complex data just like in Excel.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open the document containing your two lists.
- 2. Select the target cell: Click on the first empty cell where you want the combined list to populate.
- 3. Input the combination formula: Enter the formula =TOCOL(TEXT(A1:A365,"000")&TRANSPOSE(TEXT(C1:C10,"00"))) and press Enter.
- 4. View the results: The combined list will immediately spill downwards, applying the correct combinations and keeping leading zeros intact.
Frequently Asked Questions
Can I combine lists without preserving leading zeros?
Yes. If your data doesn't require specific number formatting, you can omit the TEXT function. The formula simplifies to: =TOCOL(A1:A365&TRANSPOSE(C1:C10)).
How do I add a delimiter like a hyphen between the combined values?
You can insert a delimiter string by adding it between ampersands in the formula. For example, to add a hyphen, use: =TOCOL(TEXT(A1:A365,"000")&"-"&TRANSPOSE(TEXT(C1:C10,"00"))).
Why am I getting a #SPILL! error when I enter the formula?
A #SPILL! error occurs when the dynamic array cannot expand because there is existing data or text blocking the cells below the formula. Clear the cells beneath your formula so the results have enough room to populate.
Is it possible to combine three lists using formulas?
Yes, but combining three or more lists requires more complex dynamic array structures such as utilizing REDUCE with LAMBDA, or using the Power Query tool to perform multiple cross joins.
