Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.
share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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