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?
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?
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
Power Query Academy!
Get & Transform (aka Power Query) is an incredible tool in Excel that does so many amazing things: consolidates, normalizes, cleans, joins, etc!
I have enrolled in Power Query Academy ! Why not join me?
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.
I’m a Data Analyst at McKinsey & Company.