One more method to find unique values in excel and you can call me a dork

I am obsessed with unique values, I guess it has something to do the quality data of I get to work with. So much so that it is the first excel post I have written on this blog, way back in 2006. Today I want to share with you all one more way (Not again man, you did that 2 weeks back) to extract unique values. I promise that I wont propose another unique idea to extract unique values (remember, I can still write about ways to remove duplicates… 😛 )

Ok, enough introduction already. Here goes another unique items extraction technique, this time using Pivot Tables.

1. Just select the data from which you need to extract unique items

2. Create a Pivot Table (in Excel 2003  menu > data > pivot table and in Excel 2007 Insert > Pivot Table)

3. Drag and drop a column in the pivot table and you will instantly see all unique values for that column.

4. Copy the values and use paste special to extract the values.

Why I love this technique:

It is scalable and works really well with large data. If your data changes just refresh the pivot. Also, when you need to extract unique values for multiple columns this technique is really good, for eg. unique customer names, product names, cities from monthly sales report. Despite the manual step of creating the pivot tables, this method is highly reliable.

Also checkout other ways to filter unique items or eliminate duplicates:

> Getting unique items using data filter and formulas

> Use advanced data filters to find unique items

> Eliminate Duplicate Entries in a List using Formulas

> Get Unique items using Excel 2007 built in features

What is your favorite way of extracting unique items ? Or you are happy with with few duplicates here and and there ?