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

Example spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AvB0HQO9X_ExdE1TZENMWDFlZkRhTThpcVlGMVNybkE

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:

  1. I can't get the unique values from the matrix that is output -- UNIQUE only lists unique rows.
  2. 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.

1 Answer 1


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:


then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.