When you support Excel users, one of the most common questions you’ll hear is, “I’ve got a list with a thousand entries in a column, and many of those are duplicates. How do I generate a list of the unique entries in that column?”
There are at least two good answers to that question. The first answer is to go to Data | AutoFilter and then click the drop-down list for the column in question. Doing so lets you see the list of unique entries onscreen. If seeing the list satisfies your need, you’re finished.
The second answer is the one to use if you want to have a list of the unique entries you can copy and paste elsewhere. To generate such a list, you’ll use Data | Filter | Advanced Filter. To demonstrate how it works, we’ll use the data in Column B from the sample sheet shown in Figure A.
Figure A
- Click on the column letter to select the entire column that contains your data and then copy it by pressing Ctrl + C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you’ll need the column header.)
- Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed. Note: You don’t have to select all the data or sort it first for this tip to work.
- Go to Data | Filter | Advanced Filter.By default, Excel will suggest filtering the list “in-place.” There’s nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
- Select the Copy To Another Location option (Figure B), select the Unique Records Only check box, and type B1 in the Copy To field.
- Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure C.
