I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma.
+-----------+
| Labels |
+-----------+
| Foo,Bar |
| Bar,Baz |
| Quux,Quuz |
I want to create a formula that generates a a column with all the unique labels separately in a row. For the example above this would be the following.
+-----------+
| Labels |
+-----------+
| Foo |
| Bar |
| Baz |
| Quux |
| Quuz |
I have figured out that I can use the SPLIT function to obtain the labels separately. If I add to that the use of ARRAYFORMULA I can get a matrix of the values. But then I'm stuck with that matrix, and how to turn all the columns into a single column. To solve that I know you can use the {RANGE, RANGE} syntax. To apply the {} to the result of an ARRAYFORMULA I use a QUERY to pick out all the columns.
This gives me the following:
=UNIQUE(
{
QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col1");
QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col2");
QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col3")
}
)
The problem with this approach is that it works for Labels that contain up to three comma-separated values, and there has to be at least one cell with three comma-separated values.
Best Answer
I think this will do just what you need.
Give it a try