Afia Kona
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.
➤ 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.
➤ 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.
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.
➤ 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.
➤ 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.
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.
➤ 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.
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.
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.
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.
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.
➤ 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.
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.
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.
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.
