Search code examples
google-sheetsunique

How can I generate a three column list of unique "combos"?

I have three columns of information. For example: color, model, year.

Can I use the "unique" instruction to generate in three new columns each unique combination for color, model, year, each in one column?

ex.

color   model    year
red     sedan    2016
red     sedan    2020
black   truck    2018

Thanks!

Solution

  • Suppose your three headers are in A1, B1 and C1 with your data running A2:C. And suppose you want the unique combinations in E:G. First, be sure that the entire range E:G is empty. Then place the following formula in E1:

    =ArrayFormula({A1:C1;SPLIT(FLATTEN(UNIQUE(FILTER(A2:A,A2:A<>""))&"|"&TRANSPOSE(FLATTEN(UNIQUE(FILTER(B2:B,B2:B<>""))&"|"&TRANSPOSE(UNIQUE(FILTER(C2:C,C2:C<>"")))))),"|")})

    The formula first reproduces the headers from A1:C1.

    The combinations are formed by first concatenating each UNIQUE model (from a list that is FILTERed to remove blanks) with each UNIQUE year (from a list that is also FILTERed to remove blanks), with a pipe symbol between each as a separator that SPLIT will later use.

    That grid of combinations is FLATTENed into a single column and then concatenated once more with a UNIQUE and FILTERed list of the colors leading off, and again with a pipe symbol as a separator. Once more, the entire grid of results is FLATTENed into a single column.

    Finally, SPLIT acts on the pipe symbols to separate the three pieces into their own columns under the headers.