How do you develop a unique list of items in excel?
Asked by
Chatfe (
432
)
January 12th, 2010
I have a long list of data that includes repeats, something like this: A, B, B, C, D, D etc. What formulas or functions can I use to do the following?
1. Count just the unique items? (in the above example, that would be: 4)
2. Create a list of all the unique items? (in the above example, that would be: A, B, C, D)
Observing members:
0
Composing members:
0
3 Answers
Ok, so highlight the cells that contain the data.
Then in the menu bar, go to Data – Filter, Advanced Filter. Choose “Copy to another location”, check the box labeled “Unique records only”, and then copy to somewhere else, where you want the list of unique items. And from that, you should be able to count how many rows you have, giving you the answer to the first question.
(Menu may slightly differ if you’re not using Office 2007) Hope this helped!
in 2007, anyway, there’s also a “Remove duplicates” on the Data tab.
If you actually need a function, you could do a complicated series of transformations, and would need the list to be sorted.
E.g., if this record is identical to the record below it, don’t count it or copy it.
What I typically do if I have a list of items in A:A is set up a new column, let’s say B:B, and enter the formula:
=if( not( isna( vlookup( A1, A1:$A$10000, 1, false)), “Dup”, ””)
This formula, copied down through all of column B:B for which you have values in A:A, (I specified here from A1 to A10000) will list a “Dup” next to each value that is duplicated later in the list. Then all you need to do is autofilter on Column B:B for “Blanks”, and you’ll have unique values in A:A.
You can also do ‘standard’ filtering to ‘filter unique values’, but I prefer to do my own.
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
