How to Extract Unique Items From a List in Excel

Microsoft Excel is a powerful tool widely used for data manipulation and analysis. Extracting unique items from a list is a common task in Excel that involves identifying and removing duplicates, leading to cleaner and more accurate data. We often need to report Unique customers/products/items from a given data for data analysis. In this article, we explore different approaches in Excel to list the unique items from a column.

This lesson explains various techniques for pulling a distinct or unique list from an Excel column. This article also describes how to eliminate duplicates from a range. One of the most frequent data-processing tasks in Excel is this one.

Why Extracting Unique Items Matters

Extracting unique items from a list is crucial for several reasons:

Data Accuracy

Removing duplicate entries ensures accurate analysis and prevents overcounting and undercounting your data.

Data Visualization

Unique items provide a clearer and more concise dataset, making it easier to create meaningful visualizations.

Data Cleaning

Extracting unique items helps clean up data, leading to more reliable insights and decision-making.

Scenario

Say you have a list of the names of the Customers. A few values on the list are duplicates. You want to get special values out of it. A distinct list would be one with unique values.

Sample Data 

We have given a list of customer names in Excel as below in column “A”.  Have to report unique customers for further analysis.

3 Ways to Extract Unique Values in Excel

1. Using Advanced Filter
2. Remove Duplicates
3. The Formula to Extract Unique Items from a List using an Array Formula

The sections below provide a detailed explanation of the above-mentioned approaches.

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 pop up the Advanced Filter dialogue box.

Step 3: Make sure the below options are selected in the “Advanced Filter” Dialogue 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”.

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 pop up the Remove Duplicates dialogue box.

Step 3: Make sure In the Remove Duplicates Dialogue box is selected, “My data has headers” and “Customer”. Then press “OK”.  

Excel will pop up below the message box, Press “OK” (Image 1).  You can see the unique list of customers in column “C” (Image 2).

Formula to Extract Unique Items from a List using an Array Formula

Excel Formulas such as INDEX, MATCH, and COUNTIF make up the formula. 

The cells that have duplicates are all included in the INDEX function in this case. When searching for data with an absolute match, we employ the MATCH function. When there is a duplicate, COUNTIF then returns 1; otherwise, it returns 0.

Scenario

Say you have a list of the names of the Departments. A few values on the list are duplicates.

There are 15 records in the collection. The information begins in cell C2 and finishes in cell C16. Cell C1 contains the list's header.

Formula 

=IFERROR(INDEX(C2:C16, MATCH(0,COUNTIF($E$1:E1, C2:C16), 0)),"")

Since this is an array formula, we must apply it by pressing CTRL + SHIFT + ENTER. If everything is done correctly, Excel will automatically enclose the calculation in curly brackets.

How this Formula Works

Using an array formula, you can perform a specific operation on a number of variables at once. In other words, we may make a computation on several values without having to manually enter the results into each cell.

Step 1. COUNTIF($E$1:E1, C2:C16)

Syntax: COUNTIF(range, condition)

It counts how many cells in a range satisfy the specified condition. If $E$1:E1 is present in C2:C16, COUNTIF($E$1:E1, C2:C16) returns 1, else 0.

Step 2. We are determining the position of the item in Step I whose array value is 0 in this step

Syntax: MATCH(lookup_value;lookup_array; [match_type]

It provides the location of a certain item in an array in relation to a given value.

Step 3. We extract the desired distinct value in this phase. The INDEX function makes it possible.

Syntax: INDEX(array,row_num,[column_num])

The reference of a cell in a specified range that matches a given row and column number is returned by the INDEX function.

In this particular case, INDEX(C2:C16, MATCH(0,COUNTIF($E$1:E1, C2:C16), 0)) returns IT

If Blank Values Are Present In a List

Let's say you want to extract unique values from a list of data that has blank or missing entries. In this situation, your formula needs to be adjusted.

Formula 

=IFERROR(INDEX($C$2:$C$16, MATCH(0,IF(ISBLANK($C$2:$C$16),1,COUNTIF($E$1:E1, $C$2:$C$16)), 0)),"")

Generic Formula to Extract Unique Items from a List Using a Non-Array Formula

Efficiently extracting unique items from a list is a fundamental data processing task, essential for accurate analysis and decision-making. While array formulas are commonly used for this purpose, there is an ingenious non-array formula that offers a simple and effective way to achieve the same result. Below is the powerful non-array formula to extract unique items from a list using the LOOKUP Function.

Formula:

=LOOKUP(2,1/(COUNTIF(exp_range, list)=0),list)

How it Works:

• The COUNTIF Function Calculates the occurrence of each value from the list within the exp_range.
• Then the resulting zeroes are compared to zero, creating an array with True and False values.
• This ARRAY is used as a divisor, with 1 as the dividend, creating a new array with 1 and Div/0 error.
• The LOOKUP Function uses the last non-error value as the lookup_vector.
• The LOOKUP Function returns the corresponding unique items from the original list effectively extracting only the distinct values.