excel formula : Find uniques in one column dependi

Question

I can do this manually but I'm sure there is a formulaic way to do this.
Here is the data :

Column-A      Column-B      Column-C
C             Y   
D             
E             Y
F
E             Y    

What I want to do is in 2 steps :
a.) Select all values in Column-A, where the corresponding value in Column-B is "Y".
b.) From the data selected from Column-A above, select only the unique values and put them in Column-C c.) Hence the data in Column-C for the above data will be "C" & "E"

Any pointers ?

Answer 1

Here's one option assuming you have excel 2007

Put this formula in C1

=IFERROR(INDEX(A1:A5,MATCH("Y",B1:B5,0)),"")

then this one in C2 copied down

=IFERROR(INDEX(A$1:A$5,MATCH(1,INDEX((B$1:B$5="y")*(COUNTIF(C$1:C1,A$1:A$5)=0),0),0)),"")

You can do it in earlier versions but it requires some longer formulas to ensure that you don't get error values once valid matches are exhausted

Explanation:

Formula 1 uses MATCH to find the position of the first "y" in B1:B5, then INDEX returns the relevant value from A1:A5. If your columns were the other way round you could use VLOOKUP, INDEX/MATCH is a standard way to do a "left lookup".

Formula 2 uses MATCH to find the position of the first row where 2 conditions are satisfied, B1:B5 = "y" and A1:A5 <> a value already found. The values already found are in column C above so the COUNTIF function looks at the cells above and does a count for each value in column A within that range above (which expands as you drag the formula down) - a count of zero means that that value hasn't already been selected. Once MATCH identifies the row number INDEX takes the value from that row in column A.