24 Nov 2015

## 00127 All Combinations No Formulas No VBA

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