How to Extract Unique Items from a List in Excel (10 Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to Extract Unique Items from a List in Excel, this article will be very helpful for you. Here, we will walk you through 10 easy methods to extract unique items from a list.

Here, we describe each of the methods step by step so that you can Extract Unique Items from a List effortlessly. We have used Excel 365. You can use any available Excel version.


1. Using Excel Array Formula to Extract Unique Items from List

The following Product List contains ID No and Product Name. We can see, there is repetition in Product Name. We want to extract unique products from that list. We will use the Array Formula to extract unique items.

How to Extract Unique Items from a List in Excel

➤ To begin with, we will write the following formula in cell  E5.

=IFERROR(INDEX($C$5:$C$12,MATCH(0,COUNTIF($E$4:E4,$C$5:$C$12),0)),"")

This formula is a combination of INDEX, MATCH, and COUNTIF functions.

  • COUNTIF($E$4:E4,$C$5:$C$12) Checks the unique list and returns a 0 when a match is not found and 1 when a match is found.
  • MATCH(0,COUNTIF($E$4:E4,$C$5:$C$12),0) Identifies the position of the first occurrence of no-match, here program it to represent with 0.
  • INDEX($C$5:$C$12,MATCH(0,COUNTIF($E$4:E4,$C$5:$C$12),0)) INDEX uses the position that is returned by MATCH and returns the item name from the list.
  • You may encounter errors when there are no more unique items. To get rid of it, we have used the IFERROR function, using the function we have replaced the error message with blank.

Extract Unique Items form a List in excel Using Array Formula

➤ After that, we have to press Enter.

➤ We have to Drag Down the formula with the Fill Handle tool

➤ Finally, we can see the unique items in the Unique Products Using Array Formula table.

Extract Unique Items from a List using Array Formula


2. Using Excel UNIQUE Function to Extract from List

We want to extract unique items from the following Product Name by using the UNIQUE Function.

How to Extract Unique Items from a List in Excel

➤ First of all, we will type the =UNIQUE in cell E5, and a UNIQUE Function will appear.

➤ We have to select an array, which is our Product Name, therefore, we select C5 to C12.

Extract Unique Items from a List in Excel Using COUNTIF function

➤ After that, we have to give a comma, ”,”, and we have to double click on False-Return unique rows.

➤ We will close the bracket and press Enter.

➤ Finally, we can extract unique items in the Unique Product List Using UNIQUE Function table. We can also see the formula in the Formula bar.

Extract Unique Items from a List in Excel using COUNTIF function


3. Using Non-Array Formula of LOOKUP and COUNTIF Functions

We can use a non-array formula that consists of LOOKUP and COUNTIF too. Let’s see how this formula helps us to extract unique items from a list.

Here, we type the following formula in cell E5.

=LOOKUP(2,1/(COUNTIF($E$4:E4,$C$5:$C$12)=0),$C$5:$C$12)
  • COUNTIF($E$4:E4,$C$5:$C$12) Checks the unique list, and returns a 0 when a match is not found and 1 if a match is found. This generates an array that consists of Binary values TRUE and FALSE. Then, divide 1 by this array which provides another array of the values 1 and  #DIV/0 error.
  • The outer LOOKUP function has 2 as the lookup value, where the result of the COUNTIF portion works as lookup_vector. Comparing these two, LOOKUP matches the final value of the error and returns the corresponding value.

How to Extract Unique Items from a List in Excel

➤ Then, we will press Enter.

➤ We will Drag Down the formula with the Fill Handle.

➤ Finally, we can see the extracted unique items in the Unique Product Using Non-Array Formula table. We can also see the formula in the Formula bar.

How to Extract Unique Items from a List in Excel


4. Extracting Excluding Duplicates Using Excel Array Formula

In this method, we will extract unique items excluding duplicates by using the  Array formula.

➤ First, we will type the following formula in cell E5.

=INDEX(List,MATCH(0,INDEX(COUNTIF(E4:$E$4,List)+(COUNTIF(List,List)<>1),0,0),0))

Here, E4:$E$4 is the first cell of the column we want to put out the extracting result, and the List is the range of selected cells from C5 to C12.

The two INDEX functions return the initial and final value from the location that was derived by the COUNTIFS and MATCH respectively.

➤ After that, we will press Enter.

➤ Then, we will drag down the formula with the Fill Handle tool.

➤ Finally, we can see the two unique products excluding duplication.

How to Extract Unique Items from a List in Excel


5. Extracting Unique Items from List Using Advanced Filter

You can use an Excel feature called Advanced Filter to extract unique items from a list. Let’s get to know how to do that.

➤ First of all, select the range of data you want to extract

➤ After that, click on the Data tab.

Here, we’ve selected the cells and explored the Data tab. You will find an Advanced option there (within the Sort & Filter group of commands).

➤ After that, an Advanced Filter window will appear.

➤ We will select Copy to another location

➤ We will give the location $E$4 in the box Copy to

➤ Make sure to click on Unique records only.

➤ Now, click on OK.

➤ Finally, we can see the unique items are extracted in the table Unique Product using Advanced Filter.

How to Extract Unique Items from a List in Excel


6. Extracting Case-Sensitive Unique Values in Excel

If we have case-sensitive distinct values like in the following List table, we can use an Array formula to extract unique items from that list.

➤ First of all, we have to type the following formula in cell D3.

=IFERROR(INDEX($B$3:$B$10, MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$10,TRANSPOSE($D$2:D2)), MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10))), 0)), "")

➤ After that, we will press Enter.

➤ We will drag down the formula by using Fill Handle.

➤ Finally, we can see extracted case-sensitive unique values in the table Case Sensitive Distinct Values.

How to Extract Unique Items from a List in Excel


7. Using Pivot Table to Extract Unique Items from List

We can extract unique items from the following Product List by using the Pivot Table.

How to Extract Unique Items from a List in Excel

➤ First of all, we will select the range of datasets from which we want to extract unique items.

➤ Here, we select data range C4 to C12.

➤ After that, select the Insert tab from Ribbon.

➤ Then, select the Pivot Table.

➤ After that, we have to select Existing Worksheet.

➤ We have to give a location. Here, we select the location E4 to E12.

➤ Mark Add this data to the Data Model

➤ Click OK.

➤ Finally, we can see that when we mark the Product name in the Pivot Table, the extracted Unique Product appears in the Row Levels table.

Extract Unique Items from a List in Excel Using Pivot table


8. Applying VBA Macro to Extract Unique Items from List

From the following Product List table, we want to extract a unique Product Name using the VBA Code.

➤ First of all, we will type ALT+F11 in our working sheet. Here, we are working on Sheet8.

➤ After that, a VBA Project window will appear.

➤ We have to double-click on Sheet8.

➤ A VBA editor window will appear.

➤ We will type the following code in the VBA editor window.

Sub ExtractUnique()
Rem Declare a Long type variable
Dim lsrow As Long
Rem Define the variable
lsrow = Cells(Rows.Count, "C").End(xlUp).Row
Rem Applying AdvancedFilter
ActiveSheet.Range("C4:C" & lsrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("E4"), Unique:=True
End Sub

Here, we have declared a Long type variable and inserted the last row within that. Then, apply several ActiveSheet methods to copy the range while keeping Unique as True.

➤ We will close the VBA editor window and will go to our active Sheet8.

➤ There we will type ALT+F8, and a Macro Name window will appear.

➤ We will click on Run.

➤ Finally, we’ll see the unique products in the Product Name table.

How to Extract Unique Items from a List in Excel


9. Highlighting Unique Items with Conditional Formatting

We want to highlight a Unique Product Name from the following Product List.

➤ First, we select the Product Name from C5 to C12.

➤ Then, we will go to the Home tab.

➤ Select Conditional Formatting.

➤ And then, select New Rule.

A New Formatting Rule window will appear.

➤ Select Use a Formula to determine which cells to format.

➤ Write the following formula in Format values where this formula is true box.

=COUNTIF($C$5:C5,C5)=1

➤ Then, click on Format.

A Format Cells window will appear.

➤ Click on the Fill option.

➤ Select a color, here, we select blue.

➤ Then, click OK.

➤ Now, see the Preview, and click OK.

➤ Finally, we can see the highlighted unique Product Name.


10. Using Conditional Formatting to Extract Unique Items

In the following Product List table, we want to hide duplicate Product Name, and we want to show unique product names only.

➤ To do so, first of all, we have to select the Product Name from cells C5 to C12.

➤ After that, we will go to the Home tab in the Ribbon, and we have to Select Conditional Formatting.

➤ Then, select the New Rule.

A New Formatting Rule window will appear.

➤ We have to select Use a Formula to determine which cells to format.

➤ We write the following formula in Format values where this formula is true box.

=COUNTIF($C$5:C5,C5)>1

➤ Then, click on Format.

A Format Cells window will appear.

➤ We will select the Font option.

➤ Then, we have to select a white Theme Color.

➤ Click on OK.

➤ We can see the Preview and click OK.

➤ Now, we can see that the duplicate product names get hidden, as they are colored white.

➤ Now, we want to sort unique products at the top of the list. Therefore, we have to right-click on any of the cells. Here, we right-click on cell C5.

➤ After that, we have to select the Filter option.

➤ Now, we have to select Filter by Selected Cells Font Color.

➤ Finally, we can see there is only the unique Product Name on the Product List table.

How to Extract Unique Items from a List in Excel


Download Workbook


Conclusion

Here, we showed you some simple, easy, and effective methods that will help you extract unique items from a list in Excel. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to contact us in the comment section.


<< Go Back to Unique Values | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo