Listing all the unique values from one column into another column

[Yoshi64]

Say I have a column with 100 rows. It is randomly populated such that 30 of the rows have values, the rest are blank. There are only 5 UNIQUE values among the 30 total values, so many duplicates.

I want a new column beside it which displays the 5 unique values in rows 1-5. So basically it eliminates the blanks and duplicates. How do I do this?

Thanks!

[popipipo]

Try a 'pivot table' or 'advanced filter'

[AlKey]

Assuming you data in column A and the rang starts in A2 and ends in E100

In B2 copy and paste this formula and drag it down

=INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$100),0,0),0))

[Yoshi64]

Thanks a bunch guys. AlKey's index forumla did the job, but I may read up on pivot tables too because they sound useful as well.

[AlKey]

You're welcome and thanks for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Yoshi64]

Quick follow up, if I drag the B2 formula down too far, I get #N/A errors. Is there a way to convert these into a 0 instead? I could even do it on the row over. Like =if(B2="any number",B2,0) or something.

[AlKey]

Sure

=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$100),0,0),0)),0)

[Yoshi64]

You rock. I really need to learn more of these excel functions. But in the meantime the help is much appreciated!

[AlKey]

Glad to be of help