Power Query Problems

How to Generate All Two-Item Combinations in Excel Without VBA

Ayan Masood Sep 28, 2026 869 views

Question details

The user wants to generate all unique pairs (two-item combinations) from a list of values in Excel without using VBA scripts.

How to Generate All Two-Item Combinations in Excel Without VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating comprehensive pairings from a single dataset for matrix analysis, comparison, or mapping purposes.
Observed behavior: Needs an automated, formulaic, or built-in tool approach (like Power Query) to pair items such as AB, AC, BC, etc., avoiding manual entry or VBA macro dependencies.

Before you start

Ensure your list of values is formatted as an official Excel Table by selecting your data and pressing Ctrl+T, then give your table a recognizable name like 'Data' in the Table Design tab.

Solution 1Recommended

Use Power Query to Generate Combinations

Power Query is the most efficient, no-code method to generate a Cartesian product of a list and filter out self-matching pairs.

By duplicating your source table as a custom column within Power Query, you can cross-join every item with every other item. Filtering out the rows where the values match leaves you with all possible two-item combinations.

Load Table into Power Query

Go to the 'Data' tab on the Excel ribbon, click 'From Table/Range' in the Get & Transform Data group. This will open your table in the Power Query Editor.

Add a Custom Column

Navigate to the 'Add Column' tab in the Power Query Editor and click 'Custom Column'. In the dialog box, simply type the exact name of your source table (e.g., = Data) and click OK.

Expand the Duplicate Column

Click the expand icon (two diverging arrows) on the right side of your new custom column header. Uncheck 'Use original column name as prefix' and click OK to reveal the duplicate 'Name' column (often named Name.1).

Filter Out Self-Matches

You will now see combinations including items paired with themselves (e.g., A and A). To remove these, click the filter arrow on the 'Name.1' column, go to Text Filters > Does Not Equal, and select the original 'Name' column from the dropdown (you may need to switch to 'Select a column' in the prompt).

Merge and Load

Hold Ctrl and select both the 'Name' and 'Name.1' columns. Right-click the headers and select 'Merge Columns'. Choose 'None' as the delimiter, click OK, then go to the Home tab and click 'Close & Load' to return the combinations to Excel.

Use Power Query to Generate Combinations

Removing Reverse Duplicates: If you want to treat 'AB' and 'BA' as the same combination and only keep one, filter the second column to only include values that are strictly 'greater than' the first column instead of just 'does not equal'.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

While Power Query is a highly specific feature of Microsoft Excel, WPS Office provides a free, lightweight, and incredibly powerful alternative for your everyday data processing and spreadsheet tasks. Enjoy advanced formulas, extensive data tools, and familiar formatting without the heavy subscription fees.

1. Download the Software: Visit the official WPS Office website to download and install the free suite on your PC or Mac.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing .xlsx files with complete format retention.
3. Process Your Data: Utilize powerful built-in formulas, pivot tables, and data validation tools to analyze and combine your datasets efficiently.

Fully compatible with Microsoft Excel formats (.xls, .xlsx, .csv)Lightweight application that opens large datasets swiftlyFamiliar user interface ensuring a zero-learning-curve transitionRich built-in formulas for complex data manipulation

microsoft office alternative - wps office

Frequently Asked Questions

Can I generate combinations using Excel formulas instead of Power Query?

Yes, if you are using Microsoft 365, you can use dynamic array formulas utilizing functions like REDUCE, TOCOL, and CHOOSEROWS to generate combinations. However, Power Query is generally easier to manage for users unfamiliar with complex array logic.

Does this Power Query method automatically update if I add new items to my list?

Yes. If you add new items to your source Excel Table, you simply need to right-click the resulting combinations table and select 'Refresh'. Power Query will automatically process the new items and output the updated combinations.

Why does my custom column in Power Query say 'Error'?

This usually happens if you mistyped the table name in the Custom Column formula. The formula is case-sensitive, so if your table is named 'Data', typing '= data' will result in an error. Ensure the spelling and casing exactly match your source query name.

Excel & Data Problems