Tom’s Tutorials For Excel: Getting Unique Items From a List
In the following picture, you see a list of names, many of which are repeated 2, 3, or more times. Here’s how you can create a shorter list to show each unique item only once.
To create a list of unique names in column C based on the list of many repeated names in column A, follow these 3 steps:
Step 1
Select any single cell in the list. In the above picture, cell A1 is selected.
Step 2
From your keyboard, press the Alt key, and while doing so, press the D and F keys. Then, press the A key as shown in the next picture.
Step 3
You will see the Advanced Filter dialog box as shown below. Click to select the options for “Copy to another location” and “Unique records only”. In the Copy to field, you see that $C$1 is entered, which you can type in manually, or click into that field and then click cell C1. After that, click OK.
And that’s it! Your list of unique items will be shown in column C similar to the next picture.
Thanks Tom. Although I use a VBA routine for this task, since long ago and it does the job well, (so this is not a painful problem) i was glad to know it is possile to do without VBA!.
Thanks Eugeny!
Don’t forget a PivotTable will also give you the unique items and these days Power Query can be used as well. All you need is to Refresh to get the latest unique list.