=AGGREGATE(15,6,1/(($A$2:$A$10="West")*($B$2:$B$10<>0))*($B$2:$B$10),1)
93. Generate a Unique List out of Duplicate Entries
Suppose, you have entries in A2:A100 and you want to generate a list containing only
unique entries in column C starting C2. You can use following formula in C2 and drag down
the formula –
Case 1 – A2:A100 doesn’t contain any blanks
=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$1:$C1,$A$2:$A$100),0,0),
)),"")
Case 2 – A2:A100 contains blanks. In this case, you will have to use Array formula.
=IFERROR(INDEX($A$2:$A$100, MATCH(0, IF($A$2:$A$100<>"",COUNTIF(C1:$C$1,
$A$2:$A$100)), 0)),"")
OR
=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:$C1,$A$2:$A$100&""),0)),"")
Note - Array Formula is not entered by pressing ENTER after entering your formula but by
pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after
pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you
can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again.
Don't put { } manually.
---- End of Document ----
