Extract a list from an array wit specific condition

[aes89]

Hello All,

I have a range of car brand names.there are repeated names in the range and it varies daily. How do I extract a listing of the names(without the repeated ones) where the cell in the adjacent range is not empty. Not sure if it can be done with excel formulas only or vba is required. Thanks.

 

[Domenic]

Let's assume that A2:A10 contains the car brand names, and B2:B10 contains the corresponding empty/non-empty cells. Try...

D2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(B2:B10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A100)-ROW(A2)+1)>0,1))

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(E$2:E2)<=$D$2,INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($B$2:$B$10<>"",MATCH("~"&A$2:A$10,A$2:A$10&"",0)),ROW($A$2:$A$10)-ROW($A$2)+1)>0,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(E$2:E2))),"")

 

[aes89]

Thank you. It works perfectly.

On a side note, it looks pretty massive. Is it possible if you could explain how it works in concept? I would like to understand it part by part if possible. Greatly appreciate it. Thank you in advance.