When working with Excel, your data may have columns with repeated values or entries. There may be times when you want to view or extract only the unique values in the column. Although you can use the Filter command to check the unique entries, you may want to want to generate a unique list of entries to another column in the worksheet or another workbook. In this Excel tutorial, I’ll show you how to extract a unique list of entries to another column in the same worksheet.
How to Extract a Unique List of Entries in Excel
In this example, I have a list of products in Column B. I am going to extract the unique values in Column B to Column D. To do this:
1. Select column B.
2. On the Data tab, in the Sort & Filter group, click Advanced.
3. The Advanced Filter dialog box is displayed. Select the Copy to another location option.
4. In the Copy to text box, enter the cell to which you want to extract the unique set of values. For the purpose of this example, in the Copy to text box, I will enter D1.
5. Check the Unique records only check box and click OK.
The unique list of entries are now displayed in Column D.
Alternately, you can also use the Duplicates command to extract a unique list of entries. To do this:
1. Copy and paste the entries in Column C in Column D.
2. On the Data tab, in the Data Tools group, click Remove Duplicates.
3. In the Remove Duplicates Warning dialog box, click Continue with the current selection and then click Remove Duplicates.
4. In the Remove Duplicates dialog box, ensure that the Column containing the data to be filtered is checked and then click OK.
5. In the Microsoft Excel message box, click OK to confirm the removal of the duplicate values.