google sheets
I have a column and each cell has multiple labels, like so:
a,b,c
b, c, d
g, h, a
So I'm looking for a formula, not code, where the formula finds the unique elements for the column,
output:
a
b
c
d
g
h
I've tried different combinations of unique(), split(), arrayformula(), transpose() but can't get it work.
=unique(ArrayFormula(TRANSPOSE(split(A1:A3,","))))
not sure how to get it into one column
I know this is an old topic, but for the number of unique articles, this might work:
=ArrayFormula(counta(unique(filter(A2:A&B2:B, len(A2:A&B2:B)))))
I also tried to work out some formulas for the other questions:
Hope this helps ?
Possible but may require some 'compromising':
=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))
in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required.
in say G1 and copied down to suit:
=arrayformula(LARGE(value(D$1:F$10),row()))
then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):
=unique(G:G)
The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0 would have come first and there are quite a few of those.
Concatenate results with ="Value "&H1 etc if required.
Best Answer
You can use this formula:
=unique(transpose(arrayformula(trim(split(join(",",A1:A),",")))))Assuming that the delimiter is always a comma - the easiest way to stack them would be to use join with the same delimiter as well.
That way when you split it, they will all be split with the same delimiter.
In order to clean up the extra spaces, you want to wrap that in an arrayformula trim around your split formula, and finally transpose the list and wrap with unique.