Excel Challenge (Kevin Lehrbass): Get & Transform to List All Unique Pairs in a Class of 10 Students

Once again, Kevin Lehrbass (MySpreadsheetLab) and I are taking on a common challenge and comparing each others' solutions.

Today's challenge: if you have 10 students in a class, and would like each student to work with a different student each week, how can you get a list of all of the unique pairs?

My favorite solution that Kevin created uses pivot tables and helper columns. Check out the details of Kevin's solutions here:

Kevin's blogpost

I present 2 solutions:
1. A Matrix solution that pairs each student.
2. Get & Transform. This uses a cross-join aka Cartesian-join and it's beautiful! I also show how easy it is to get the unique pairs from 22 students.

This video takes you through a detailed approach to solving this problem in Excel.

ModelOff Global Training Camp - Toronto

My book: Guerrilla Data Analysis 2nd Edition

[joannpaules]

A pairing of JoAnn and JoAnn would either be freaking awesome - or REALLY scary! Thank you for the early morning chuckle!

[jasonm]

Damn Oz, getting dangerous with the Get & Transform.

[erendiz]

the formula you have used is correct for r=2. for the general case it is not, use in those cases the formula as follow: n*(n-1)...(n-r+1)/(r!) so for pairs r=2! and for triples (trios) r = 3! = 6 and for quadruples r =4! = 24 etc...

[excelisfun]

Thanks for the video! You and Kevin called me out in a tweet. I do list all combinations in my stats class. I have a couple of videos :
Excel 2013 Statistical Analysis #26: Combinations & Permutations and Listing Sample Points
Excel 2013 Statistical Analysis #44: Sampling Distribution of Xbar and the Central Limit Theorem
Your Power Query example with Cross Join on Indexed tables and then the two conditional columns is great! Thanks!