CODEWITHSUNDEEP
arrayssortinggoogle-sheetsunique
Gronk
Gronk

Reputation: 391

Google Sheets - Join unique cells in a row

I am trying to get only unique values while using the function

=JOIN(", ",UNIQUE(FILTER(D$1:D$230,E$1:E$230=G1)))

It is working, except that some of the values it is grabbing are arrays themselves, so from one of my filter's data sets:

22; 14, 21; 15; 6; 19; 1, 13; 1; 19; 14 (each set between colons are on a separate row)

using the join function mentioned above, I end up with a cell with the values (lets call it value-set Alpha):

{22, {14, 21}, 15, 6, 19, {1, 13}, 1, 14} (it's not written as such, but I know that is how it is handling it)

As you can tell, it is preventing the second 19 from appearing as it is no longer unique, but I am unable to break down the array of {14, 21} to prevent 14 from appearing twice. I thought I could use the function =split(I1,",") on the value set Alpha above, which split every number up into it's own cell and then join it back displaying only the unique sets, but I cannot for the life of me figure out how to join it together without including duplicates. Any help is appreciated.

Upvotes: 1

Views: 1862

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

Try this:

=ArrayFormula(JOIN(", ",UNIQUE(QUERY(FLATTEN(TRIM(SPLIT(FILTER(D1:D230,E1:E230=G1),","))),"Select * WHERE Col1 Is Not Null"))))

If, in fact, your Col-D and Col-E ranges run the full column, top to bottom, you can shorten things a bit:

=ArrayFormula(JOIN(", ",UNIQUE(QUERY(FLATTEN(TRIM(SPLIT(FILTER(D:D,E:E=G1),","))),"Select * WHERE Col1 Is Not Null"))))

In any case, you see the FILTER at center.

SPLIT splits at any commas.

TRIM removes any leading spaces the result as of that SPLIT.

FLATTEN forms one column of all results. Understand that it will flatten the entire virtual grid, which will be as many columns wide as the largest number of comma-separated elements in any cell from the Col-D range. If your max elements was two, then the virtual grid formed by SPLIT will be two columns wide — with most of the rightmost cells blank. So when you FLATTEN, those blanks will still be interposed into the list. (See below.)

QUERY removes the blanks as described above, tightening the list to a contiguous vertical array of remaining non-null values.

UNIQUE weeds out duplicates.

Finally, JOIN joins with comma-space.

Upvotes: 2

Related Questions