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 ?
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.
