Oz du Soleil and I have been trading Excel ideas for a couple of years now. During ModelOff’s Global Training Camp in Toronto (Sept 22&23) I met Oz in person! Oz flew in from Oregon to deliver one of the Excel sessions. At one point Oz asked the following question: “Given a list of 10 names, how can Excel display all the name pairs?”
Oz and I agreed to investigate different ways that Excel could solve this.
In other words, it was another HEAD TO HEAD CHALLENGE!
Name List & Rules
- Names are stored in an Excel Table that can be easily extended or shortened
- Currently, with 10 names, we would have 45 name pairs
- Solutions to be extendable for up to about 25 names
- Name pair order doesn’t matter (“Helena & JoAnn” is the same as “JoAnn & Helena”)
In the picture above the name pairs are displayed horizontally in order to fit nicely on the screen. Ideally solutions would create a vertical list.
Cartesian Product
My quick response was to use a cartesian product in a database. In a database joins are essential for properly connecting table/queries together. The lack of a join creates a cartesian product which is all combinations of all the values. The challenge is that we have to solve this in Excel not in a database.
Get & Transform Solution
Even before Oz left Toronto I noticed that he was working on a solution using Excel’s Get & Transform feature. Oz loves Get & Transform and has created several YouTube videos about it. Why didn’t I think of that?!
Prior to Excel 2016 Get & Transform (aka Power Query) was an add-in meaning that most people wouldn’t be aware of it. Starting in Excel 2016 Get & Transform is a built in feature. So if your entire audience has Excel 2016 then you can share a file and easily re-run the query. It’s arguably the best new feature in many years.
How will I solve this?
I missed the quick home run solution using Power Query. I’ll have to try something different. As an additional challenge to myself I decided that I wouldn’t do any research (no google, no books). Just Excel and my imagination.
VBA Solution?
Of course it’s possible. But I didn’t feel like spending hours creating vba code (remember no google!).
Download my Excel file here or from my onedrive
Watch my YouTube Video Watch Oz’s YouTube Video
Brute Force Solution
Considering that we want all the name pairs for only 10 names (flexible to 25 names or so) I thought of a low tech solution! Using INDEX and RANDBETWEEN functions I created several hundred name pairs (including a helper column with names concatenated like this “Donald & Hilary”). Yes this will create a a lot of duplicates but copy/paste as values and use Excel’s Remove Duplicates feature. A formula can verify count of unique pairs. Boring solution? Yes but it works.
2 Cup of Coffee Formula Solution
What about a fully dynamic solution? No queries to re-run, no pivot to refresh, no macro to run. A solution that would work in any version of Excel! Just add/remove names and voila the solution updates. It required a series of formula helper columns but I proved that it’s possible! It was a fun challenge.
Pivot Table & Formula Solution
I remembered a trick using Pivot Tables that would create name pair combinations. It’s not 100% perfect. Formula helper columns are required to fix a few things. A final column with a filter completes this interesting solution. It’s almost fully dynamic. After adding or removing names from the list just refresh the pivot table. Probably my least favorite solution but it does work.
Who won? ….it doesn’t matter. We all win with Excel !
Well…that was fun. Until the next exciting Excel adventure!
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish and playing Chess.