This was weird. I stumbled across my video and I thought “WHAT? Get all text combinations using just a pivot? How did I do that?” It seemed like ‘Back To The Future’ as I re-watched my own video.
Now it’s all coming back…Why do I like this solution? Because it’s so easy!
All Combinations of What?
We want all combinations of ‘Fruit’, ‘Size’, and ‘Company’.
If we get more Fruit, Sizes, or Companies we need an easy way to update our solution without re-doing it from scratch.
What Should the Result Look Like?
You should end up with a data set that looks like this (this is a partial list) that shows all the combinations of the items from ‘Fruit’, ‘Size’, and ‘Company’.
How many rows should we have?
6 Fruits X 2 Sizes X 6 Companies = 48
All Combinations: Different Methods
Let’s review various methods to get all combinations from various fields:
- Excel formulas (possible but takes time to set-up)
- Excel VBA (relatively easy for a programmer but still takes time)
- SQL Cartesian product (easy to create if you have a database)
- Pivot Table (‘Show Items with no data’)
So why is the Pivot Table method so easy?
Because you don’t have to be very technical. Yes, I’ve solved this many times with formulas but sometimes I get tired and think of easier ways to solve Excel puzzles.
Here are the steps:
- 1) Convert data into a table (Highlight data: ‘Insert’ ‘Table’)
- 2) Pivot: ‘Insert’ ‘Pivot Table’ and then drop all fields into row label area
- 3) Pivot: ‘Show in Tabular Form’ & ‘Repeat All Item Labels’
- 4) Pivot: Turn off ‘Subtotals’ and ‘Grand Totals’
- 5) Pivot: Right click in Pivot, ‘Field Settings’, ‘Show Items with no data’
- 6) Pivot: Filter out blanks in each field
- 7) Double check results using formulas
Watch My YouTube Video
Download my Excel file
Download here or via my OneDrive (file 00127)
Power Query Academy!
Get & Transform (Power Query) is an incredible tool in Excel that does so many amazing things: consolidates, normalizes, cleans, joins, etc!
Excel’s newest amazing data tool!
I have enrolled in Power Query Academy ! Why not join me?
See my recommended Excel Training page.
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.
I’m a Data Analyst. Check out my YouTube videos and my blog posts.
Pingback: All Combinations Using Power Query | My Spreadsheet Lab
Thanks for posting this. When I use the pivot table and add the row values, it is changing the sort order instead of keeping it (necessary) how it was entered in the original cells. Is there a way to keep the line items in the order they are in the table that the pivot is based off of? I tried both options in the pivot table settings and neither maintains the original.
Thanks in advance. Your original column list show show alphabetical values staring A, B S, O but we never see beyond B as the pivot table is building if you have the same sorting anomaly.
hello kevin, When i use the steps for combination in my list it goes into a Excel(Not Responding) mode and gets stuck. Pls let me know what is the reason for this..
Hi Dominic,
It could be because you have too many combinations and Excel can’t handle it (memory limitations).
In a database when trying to get all the combinations between two tables (a cartesian product) the same thing can happen if there are too many combinations.
I hope this helps and thanks for visiting my blog. Stay safe Dominic.
Regards,
Kevin