|
|
|
|
jkp-ads.com Newsletter for May 2019
What I've been up to
It has been a while since my last newsletter. I've been quite busy doing other stuff.
Amsterdam Excel MVP Meeting
I've set up our annual Excel MVP meeting in Amsterdam. On May 16 and 17 we had a great two days of Excel presentations. Microsoft was kind enough to send a product manager from the Israel Excel team to our meeting so we could passionately complain about everything we would love to see improved in the product. The PM took away a truck-load of "suggestions" from us.
Here is a picture of the group.
Combinations, Combinations
Now here's a trick I learned from that meeting. The trick requires PowerPivot, but if you use Excel 2016 (or 365) you're good to go.
Suppose you have a list of products and a list of customers and you wish to create a two-column list of all possible combinations of those two. How would you do that?
Step 1, create the lists
As always we need sample data. Type Product Name in cell A1 and Product 1 in A2. Drag the fill handle from A2 as far down as you want products. I went to A10 to keep it short & simple:
Now let's convert this into a table (it looks like one, so format accordingly). From the Home tab, click the Format as Table drop-down and pick a format you like:
Remember to name the table, so it is easier to find it elsewhere in Excel:
I've named it tblProducts.
Repeat the above to create a Customers table, you should end up with something like this:
Step 2: Create a Pivot Table
Click in either table and choose Insert, Pivot Table:
a dialog pops up which looks like this:
Make sure you check the box next to "Add this data to the Data Model". Click OK.
You should now have an empty pivot table and the pivotTable Fields task pane should also show up.
Step 3, Use both tables in the same pivot table
Click on the "All" tab as indicated above. You should now see both tables you just created. Drag the only field each table has to the Row area of the pivot table task pane:
After dragging both the Customer names and the Product names fields to the row area, you should have a pivot table which looks like this:
We're almost there. To get a two-column list of all combinations, turn on "Repeat All Item Labels", which is on the Design tab of the PivotTable Tools contextual ribbon tab, under the Layout group, Report Layout button:
Done!
|
|
|
|
|
|
|
|
|
