How to Extract Unique Items From a List?
We often need to report Unique customers/products/items from a given data for data analysis. In this article, we explore two different approaches in excel to list the unique items from a column.
Sample Data: We have given a list of customer names in excel as below in column “A”. Have to report only unique customers for further analysis.
Approach 1: Remove duplicates
Follow the below steps to extract unique items from a list by removing duplicates:
Step 1: Copy the entire data from column “A” to Column “C”.
Step 2: Go to Data >> Click “Remove Duplicates” – to popup the Remove Duplicates dialog box.
Step 3: Make sure In Remove Duplicates Dialog box is selected “My data has headers” and “Customer”. Then press “OK”.
Excel will popup below the message box, Press “OK” (Image 1). You can see the unique list of customers in column “C” (Image 2).
Approach 2: Using advanced filter
Follow the below steps to extract unique items from a list using advanced filters:
Step 1: Select the entire column “A” data.
Step 2: Go to Data >> Click “Advanced” – to popup the Advanced Filter dialog box.
Step 3: Make sure the below options are selected in “Advanced Filter” Dialog box
Action : “Copy to another location” List Range : $A$1:$A$20 Copy to : Sheet1!$E$1 and selected “Unique records only” Press “OK”
You can see the unique list of customers in column “E”.