I have a spreadsheet that looks like this:
Column A | Column B | An unimportant value 1 | Value 1, Value 2 2 | 3 | Value 3 4 | Value 3, Value 1 | Don't want this 1 | 2 | Value 5, Value 6, Value 2 | Don't want this 1 | 2 | Value 7
I'd like to extract the unique values from Column B that have a value in Column A and end up with
Value 1 Value 2 Value 3 Value 5 Value 6 Value 7
So far I have a formula that splits up that column and spits out the matrix of values:
=arrayformula(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","))
I've run into two problems:
- I can't get the unique values from the matrix that is output -- UNIQUE only lists unique rows.
- For some reason the first row split only gets the first value. Notice that Value 2 is never mentioned in the output in the example spreadsheet.