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

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.

Download Workbook

10 Methods to Extract Unique Items from a List in Excel

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.


Method-1: Extract Unique Items from a List Using Array Formula

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 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

Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)


Method-2: Using 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 see extract unique items in the Unique Product List Using the UNIQUE Function table. We can also see the formula in the Formula bar.

Extract Unique Items from a List in Excel using COUNTIF function

Read More: Find Unique Values in a Column in Excel (6 Methods)


Method-3: Using Non-Array Formula of LOOKUP and COUNTIF Functions

We can use a non-array formula that is consisted of LOOKUP and COUNTIF too. Let’s see how this formula helps us to extract unique 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 the 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 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 a Non Array Formula table. We can also see the formula in the Formula bar.

How to Extract Unique Items from a List in Excel

Read More: How to Extract Unique Values Based on Criteria in Excel


Method-4: Extract Excluding Duplicates Using 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 Fill Handle tool.

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

How to Extract Unique Items from a List in Excel

Read More: How to Get Unique Values from Range in Excel (8 Methods)


Method-5: Extract Unique Items from a 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 command).

➤ 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

Read More: How to Find Unique Values from Multiple Columns in Excel


Method-6:  Extract Case-Sensitive Unique Values

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


Method-7: Pivot Table to Extract Unique Items from a 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 dataset 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, extracted Unique Product appears in the Row Levels table.

Extract Unique Items from a List in Excel Using Pivot table


Method-8: VBA to Extract Unique

From the following Product List table, we want to extract unique Product Name using 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

Read More: Excel VBA to Get Unique Values from Column (4 Examples)


Method-9: Highlight Unique Items

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

➤ First, we select 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.


Method-10: Conditional Formatting to Fetch 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 white Theme Color.

➤ Click on OK.

➤ We can see the Preview and will 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


Conclusion

Here, we showed you some simple, easy, and effective methods that will help you to 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 know us in the comment section.

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo