Microsoft Excel Hints and Tips

Question
I have a list of data (values and / or text) in cells A1:A20 - some of the cells contain duplicate data and I would like to extract the unique items and display them in column B, but without using Advanced Filter. How do I do this using a formula ?
 

Solution
In cell B1 type =IF(ISERROR(SMALL(C:C,ROW())),"",INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0))) and then copy the formula all the way down to cell B20.

In cell C1 type =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW())) and then copy the formula all the way down to cell C20.

You can download a spreadsheet with an example here.

Back to Questions Index

Return to Excel Exchange homepage.