Calculating all possible variations of a multi-select column

LaureNoel36

I have a multi-select column where I am selecting all the teams I need to reach out to about a particular row of data. I want to streamline the number of meetings I schedule, so I am trying to calculate how many unique meetings I would need to create based on the different team combinations present. For example if I have 5 rows the column could have the following values:

Row 1: I & E, FABS, PACs

Row 2: Global Strategies, PACs, Business Finance

Row 3: I & E, FABS, PACs

Row 4: PACs

Row 5: PACs

The number of meeting I would need would be 3 - one with I & E, FABS, and PACs, one with Global Strategies, PACs, and Business Finance, and one with PACs. I have hundreds of rows with different variations of these team names and want to figure out the unique meetings I would need based on those different variations.

I was trying to create reports for each unique combination and can do it, but the process of doing so assumes I know all the combinations. Just trying to see if there is a more efficient way to get that information.

jmyzk_cloudsmart_jp

Hi @LaureNoel36

Thank you for sharing an interesting real-world question.

The demo solution below assigns each group a unique prime number, then uses the product of primes to identify the combination.

Suppose your multi-select column has a list like the one below.

I first created a helper sheet to identify possible unique combinations by matching the group name to a prime number.

https://app.smartsheet.com/b/publish?EQBCT=3c4b2f1056bc4dfc9c9e8610fa39e843

Then, I added helper columns like the image below to the sheet with Teams Multi-Select Dropdowns.

https://app.smartsheet.com/b/publish?EQBCT=99ae3a65b00e4acbadab15fbd824081a

The G1 to G20 correspond to Group or Team 1 to 20's prime numbers.

The formulas for those are;

[G1] =IF(CONTAINS(INDEX({Group List with Prime Numbers : Group Name}, 1), [Teams Multi-Select Dropdown]@row), INDEX({Prime Number}, 2), 1)
[G2] =IF(CONTAINS(INDEX({Group Name}, 2), [Teams Multi-Select Dropdown]@row), INDEX({Prime Number}, 3), 1)
[G3] =IF(CONTAINS(INDEX({Group Name}, 3), [Teams Multi-Select Dropdown]@row), INDEX({Prime Number}, 4), 1)

. . . .
[G20] =IF(CONTAINS(INDEX({Group Name}, 20), [Teams Multi-Select Dropdown]@row), INDEX({Prime Number}, 21), 1)

The meaning of the formula is, for example, for [G3] if the third Group Name is contained in the selected multiple dropdown list, return the prime number in the next row, in this case, 5; otherwise, return 1.

The Group Combination Number is the product of [G1] to [G20] for [G1]*[G2]* . . .[G20].

If you assign each group a unique prime number, then the product of primes in any combination will uniquely identify that combination (since prime factorization is unique).

Lastly, the Distinct Group Combination sheet below dynamically populates the Distinct Group Combination Number and the Combination with the following formula;

=IFERROR(INDEX(DISTINCT({Group Combination Number}), Row@row), "")

=INDEX({Teams Multi_Select Dropdown}, MATCH([Distinct Group Combination Number]@row, {Group Combination Number}, 0))

https://app.smartsheet.com/b/publish?EQBCT=47513f03868d4514ad415ddea8de1b31

I wish I could think of a simpler way, but this is what I came up with.

If you need a copy of the solution, please contact me by email on my profile page.