Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.
Make Cartesian product of two tables in Excel
Note: You need Excel 2013 or above for this.
- Convert two lists to tables, if not already done.
- Select any cell in one of the tables and go to Insert > Pivot Table (Use ALT + NV shortcut)
- Make sure to check “Add this data to the Data Model” option before clicking ok.
- From your pivot table field list, switch to ALL view.
- Add both (or all fields) to row label area.
- Now, change the pivot table layout to “Show in tabular form” and check “Repeat all item labels” option.
- Turn off sub totals & grand totals.
- Viola, your cross product is ready. All combinations are generated by Excel for you. Use them as you see fit.
More fun ways to use Pivot Tables
Pivot tables versatile. You can use them to replace formulas, analyze data, create interactive dashboards and do several cool things. Check out below tutorials to get started.
24 Responses to “How to generate all combinations from two separate lists [Pivot Table Trick]”
I still use Excel 2007. A work around for this for 2 lists would be to place the Products in Column A - Starting cell A2 (going down the rows). Then place the Customers in Row 1 starting cell B1 (going across the columns). Then Add a formula in B2 =$A2&" "&B$1. Dragging it across all columns & rows where the Customers & Products are present in column A & Row 1.
If using Excel 2010, a similar solution is to first make a single pivot table from the two (or more) lists. This may require some helper columns if your lists are not already adjacent to each other. Add each list to the row labels and for each field go to "field settings"--> "Layout& Print" and select "repeat item labels" as well as "Show items with no data". Then go to "PivotTable Options"--> "Display" and select "Classic PivotTable layout". Voila! Note: if your lists are of different size, you will end up with some "(blank)" rows, but these are easy to filter out using the default filters in the pivot table.
That is awesome. Never knew it. 🙂
Awesome. Further, if lists are of different size, Place the list with lower items at the top in row labels so that all the blanks will go to bottom of the report.
Wow thank you I am still learning excel 10 and this just saved me so much time! I needed each list in its own column so this worked great
When click to insert pivot table, tick on "Add this data to the Sata Model". Hit an error message:
We couldn't get data from the Data Model. Here's the error message we got: Invalid pointer array
Pls advise.
Its not working, add this data to Data Model is grayed out. It not active and does not give me the option to check the box
Hey, could you explain me how to do that? I'm not getting the formula for import the column that I want from the other table/query.
In Power Query load both tables as connection. Then in any one of the query "add custom column" and mention =[column name from first query] and then click ok.
Done. no more steps required.
Hey, how to you mention the column name from the other query? Can you put exactly the syntax? I'm not being able to add the other query to then import the column needed. Cheers
Sir
Tried but couldn't succeed.... I am missing somewhere....
can you please mail me the sample sheet ...
Regards
Venkat
Tried. never knew it before. awesome. thanks chandoo
thank you sir,
Sryy sir but I want to know one SHORTCUT KEY plz help me.
if there is Two sheet in excel and I formulate in such a way that if I made changes in sheet 2 than automatically same change made in sheet 1.
There is Shortcut key CTRL + [ for jump in sheet 2 directly from sheet 1. But I don't know Shortcut key for jump in sheet 1 from sheet 2.
anyone.......!?
@Darshan
Have you tried
Ctrl+Page Up
Ctrl+Page Down
@Hui...
Thanx for reading/reply..
But my question was different...
ex. SHEET 2, 1+2=3
SHEET 1, 3 (Automatic change when I made change in sheet 2).
now if I made changes in sheet 2 like 2+2 = 4 than in sheet one it
reflect 4.
so now if I want to see how that 4 comes in sheet 1 than I use Ctrl + [ ,
but I don't know how I jump from sheet 2 to sheet 1 where I put formula.
@Darshan
Goto the Formula Tab
Click on Trace Dependents
There probably isn't a shortcut key as multiple cells could be dependent on the 2 cells.
Trace dependents brings up a dialog showing all the dependent cells
THANK YOU SO MUCH THIS WORKS LIKE MAGIC
xxxxxx
You are a life-saver !
Very helpful thank you. I used it for creating a time series since I can use it to create a set of period headers for all possible combinations of control columns where for many periods there is no data. I created two tables from the data, one for the data and another for the survey period copied from the first table, then combined the two in the pivot table. A better way of creating a time series is at https://www.rwalker.info/blog/2017/4/26/how-to-quickly-create-time-series-pivotcharts, but that requires a professional version of Excel, which I don't have and don't feel inclined to pay for.
This is awesome - it really helped 🙂 thanks a million!
Brilliant and simple, just like Darwin's Origin of the Species!
Thank you Chandoo, you work is wonderful. I have been cracking my brain on joining 2 unrelated tables and I found you solution in this posting. This is so wonderful!
Please keep posting and continue to good work!
How to generate all combinations from two separate lists [Pivot Table Trick]
I knew it can be done in some smart way! Thanks a million!
Came across this old thread, excellent by the way, my question if can you or how do you chart Cartesian results? Thanks