Combine columns in Excel and generate all possible combinations

Excel consolidate

Having two different data sets, for example a customers list and a products list, and want to combine them to get a new list with all possible combinations of customers and products?

The solution is pretty simple, feasible with MS Excel in a few minutes, and, even with thousands of entries, doesn't require much effort – much less than copy pasting the second list for each value of the first list – this last solution would take hours with hundreds of entries. See below how to combine Excel columns and generate all possible combinations Excel.

How to generate or list all possible permutations in Excel

• Create a numeric identifier from 1 to the number of entries for each source file, and, in the result file:
• Create a numeric identifier (ID) in column A going from 1 to the multiplication of the count of first file entries and second file entries, creating as many lines as the combination of both data sets,
• Add a column, enter this formula and expand it to the last line =ROUNDUP(A2/[Second file entries count];0),
• Add a column, enter this formula and expand it to the last line =A2-([Second file entries count]*(B2-1)),
• Add as many columns as you want to get from first and second files, and do vlookups on corresponding identifier in result file and source files.

Consolidate data in Excel

See below a full example to combine excel sheets columns, with a Customers list and a Products list.

Start by creating identifiers in both file, by adding a column on the left, entering values 1 and 2 in the first two lines, selecting the two cells, moving the mouse cursor to the bottom-right corner on the selection, and double clicking on the + sign to expand the identifier increment to the last line.

As a result, the identifiers have been incremented up to the last line.

How to combine columns in excel

Take the maximum identifier numbers of both Customers (C) and Products (P). Create a new file, with a column identifier, and repeat the identifier expand operation, down to the line (C * P) + 1. In below example, 7 different values for Customers, and 7 for Products, resulting in 7 * 7 = 49 combinations, + 1 line to make up for the header line.

How to combine cells in excel

In column B, we'll put the P lines per Customer, as each of them will have one line per Product. This is done by using below formula in the sheet's second line, and expanding it to the bottom (remember the double click on the + icon in the bottom-corner of the cell selection to do so), where X is the second file entries count

In column C, we'll put a count for each of the Customers, from 1 to Products count. Same operation as before, with another formula (current line identifier minus the count reached for previous Customer line), X being the second file entries count

Combine multiple columns in excel into one column

Check if it have worked. Customers are repeated P times, and for each of them, the Products identifier are repeated from 1 to P

Generate all permutations

And that's it! Then, for each column you'd like to take over from Customers or Products file, add a new column, and perform a Vlookup on the corresponding identifier and source file.

Complete 2019 Excel for Beginners in video