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:
What is your favorite way of extracting unique items ? Or you are happy with with few duplicates here and and there ?
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Best of PHD – Jan 2009||Employee Satisfaction Surveys using MS Excel »|