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

Let’s assume a scenario where we have an Excel file that contains information about the products that a country exports to different countries in Europe. We have the Product name, exported Amount, and the Country to which the product is exported. We will find every unique product that this country exports and each distinct country that this country exports the product to.

Excel Get Unique Values from Range


Method 1 – Use Advanced Filter to Get Unique Values From a Range

Steps:

  • Go to the Data tab.
  • Select Advanced from the Sort & Filter section.

Advanced Filter to Get Unique Values From Range

  • A new window titled Advanced Filter will appear. Choose Copy to another location as Action.
  • In the List Range box, select the range you want to extract the unique values from. In this example, we are trying to get all the unique or distinct products under our Product column (B5:B20). So, our List Range will be $B$5:$B$20. $ signs have been inserted to make the cell references absolute.

List Range will be $B$5:$B$20

  • In the Copy to box, select a range where the unique values will be. We have selected the range E5:E20. Check the box with the title Unique records only.
  • Click OK.
  • You will get all the distinct products in the Unique Products column (E5:E20).

Distinct Products in the Unique Products Column


Method 2 – Insert the INDEX and MATCH Formula to Get Unique Values From Range

Steps:

  • Select cell E5. Use this formula in the cell:
=INDEX(B5:B20,MATCH(0,COUNTIF($E$4:E4,B5:B20),0))
Formula Explanation

The driving force of this formula is the INDEX function which will perform the basic lookup.

=INDEX(array, row_num, [column_num])  

INDEX function has two required arguments: array and row_num.

So, if we provide the INDEX function with an array or list as the first argument and a row number as the second argument, it will return a value that will be added to the unique list.

We have provided B5:B20 as the first argument. But the hard part is to figure out what we will give the INDEX function as the second argument or row_num. We have to choose the row_num carefully so that we will only get unique values.

We will achieve this using the COUNTIF function.

=COUNTIF($E$4:E4,B5:B20)  

The COUNTIF function will count how many times items in the Unique Product column appear in the Product column which is our source list.

It will use an expanding reference. In this case, it is $E$4:E4. On one side, an expanding reference is absolute, while on the other, it is relative. In this scenario, the reference will extend to include more rows in the unique list as the formula is copied down.

Now that we have the arrays, we can start looking for row numbers. To find zero values, we use the MATCH function, which is set up for the precise match. If we use MATCH to combine the arrays generated by COUNTIF, the MATCH function locates the items while searching for a count of zero. When there are duplicates, MATCH always returns the first match. So, it will work.

Finally, INDEX provides the positions as row numbers, and INDEX returns the name at these positions.

Note: This is an array formula. You must press Ctrl + Shift + Enter together to apply the formula in a cell. It will put two curly braces around the whole formula. Newer Excel versions will work with Enter only.

Insert the INDEX and MATCH Formula to Get Unique Values From Range

  • You will get the value Apple in cell E5.
  • Drag the fill handle downward to apply the formula to the rest of the cells.

Excel Get Unique Values from Range

  • We will get all the unique values in the Unique Products.

Unique Values in the Unique Products Column


Method 3 – Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells

We have removed some of the products from the range.

Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells

Steps:

  • Use the following formula in Cell E5.
=INDEX(B5:B20, MATCH(0,IF(ISBLANK(B5:B20),1,COUNTIF($E$4:E4, B5:B20)), 0))
Note: This is an array formula. You must press Ctrl + Shift + Enter together to apply the formula in a cell. It will put two curly braces around the whole formula. Newer Excel versions will work with Enter only.

Excel Get Unique Values from Range

  • You will get the value Apple in cell E5.
  • Drag the fill handle down to apply the formula to the rest of the cells.

Drag the Fill Handle to Apply the Formula to the Rest of the Cells

  • We will get all the unique values in the Unique Products.

Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells


Method 4 – Use the LOOKUP and COUNTIF Formula to Get Unique Values From a Range

Steps:

  • Select cell E5. Insert the following formula in it:
=LOOKUP(2,1/(COUNTIF($E$4:E4,$B$5:$B$20)=0),$B$5:$B$20)
Formula Explanation

The structure of the formula is similar to that of the combination of the INDEX and  MATCH formula above, but LOOKUP handles array operations natively. The LOOKUP function takes three arguments exactly.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

COUNTIF produces a count of each value in the expanding range $E$4:E4 from the range $B$5:$B$20. Then the count of each value is compared to zero and an array consisting of TRUE and FALSE values is generated.

Then the number 1 is divided by the array, resulting in an array of 1s and #DIV/0 errors. This array becomes the second argument or the lookup_vector for the LOOKUP function.

The lookup_value or the first argument of the LOOKUP function is 2 which is greater than any of the lookup vector’s values. The last non-error value in the lookup array will be matched by the LOOKUP.

LOOKUP returns the corresponding value in result_vector or the third argument for the function. In this case, the third argument or the  result_vector is $B$5:$B$20.

Note: This is an array formula. You must press Ctrl + Shift + Enter together to apply the formula in a cell. It will put two curly braces around the whole formula. Newer Excel versions will work with Enter only.

Use the LOOKUP and COUNTIF Formula to Get Unique Values From Range

  • You will get the value Apple in cell E5.
  • Drag the fill handle down to apply the formula to the rest of the cells.

Excel Get Unique Values from Range

  • We will get all the unique values in the Unique Products.

Unique Values in the Unique Products Column


Method 5 – Use the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once

We have modified our Excel worksheet so that we the products Blueberry and Carrot appear only once in our worksheet. We will get these two unique values that appear only once in our worksheet.

Steps:

  • Select cell E5 and insert the following formula:
=LOOKUP(2,1/((COUNTIF($E$4:E4,$B$5:$B$20)=0)*(COUNTIF($B$5:$B$20,$B$5:$B$20)=1)),$B$5:$B$20)

Perform the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once

  • You will get the value Carrot in cell E5.
  • Drag the fill handle down to apply the formula to the rest of the cells.

Drag the Fill Handle to Apply the Formula to the Rest of the Cells

  • We will get the 2 unique values that appear once only in cells E5 and E6 under the Unique Product. The rest of the cells below them will show the #N/A value.

Perform the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once


Method 6 – Use the UNIQUE Function to Get Unique Values in the Range

Microsoft Excel 365 has a function called UNIQUE that returns a list of unique values in a specific range.

Steps:

  • Select cell E5. Use the following formula in the cell.
=UNIQUE(D5:D20)

Use the UNIQUE Function to Get Unique Values in the Range

  • If we press Enter, we will get all the unique countries in our Unique Country column.

Excel Get Unique Values from Range


Method 7 – Run a VBA Macro Code in Excel to Get Unique Values in the Range

Steps:

  • Select Visual Basic from the Developer or press Alt + F11 to open a VBA window.

Run a VBA Macro Code in Excel to Get Unique Values in the Range

  • Click on the Insert button and select Module.

Click on the Insert Button and Select Module

  • Insert the following code in the module:
Option Explicit
 Sub GetUniqueValues()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
       ActiveSheet.Range("B5:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("E5"), _
    Unique:=True    
End Sub
  • Click on the Run button to execute the code.

Excel Get Unique Values from Range

  • We will get all the unique products in the Unique Products column.

Unique Products in the Unique Products Column


Method 8 – Remove Duplicates in Excel to Get Unique Values in the Range

Steps:

  • Select all the cells under Country.

Remove Duplicates in Excel to Get Unique Values

  • Paste the range in the adjacent Unique Country. Select the new column.

Remove Duplicates in Excel to Get Unique Values in the Range

  • Select the Remove Duplicates option from the Data tab.

Click on Remove Duplicates Button

  • A new window titled Remove Duplicates Warning will appear. Select Continue With Current Selection.
  • Click on Remove Duplicates.

Click on the Remove Duplicates Button

  • We will see our Unique Country column has only 4 unique countries in it.

Excel Get Unique Values from Range


Things to Remember

  • The INDEX and MATCH functions together are an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.
  • While using the Remove Duplicates feature to get unique values from the range, we have selected only the Unique Country But you can add more columns or select all the columns by selecting the Expand the selection option. But if you expand the selection to add more columns, then the Remove Duplicates feature will not remove any value unless it finds two or more rows with identical data.

Download the Practice Workbook


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo