by Svetlana Cheusheva, updated on
This is the final part of the Excel Unique Values series that shows how to get a list of distinct / unique values in column using a formula, and how to tweak that formula for different datasets. You will also learn how to quickly get a distinct list using Excel's Advanced Filter, and how to extract unique rows with Duplicate Remover.
In a couple of recent articles, we discussed different methods to count and find unique values in Excel. If you had a chance to read those tutorials, you already know how to get a unique or distinct list by identifying, filtering, and copying. But that's a bit long, and by far not the only, way to extract unique values in Excel. You can do it much faster by using a special formula, and in a moment I will show you this and a couple of other techniques.
Tip. To quickly get unique values in the latest version of Excel 365 that supports dynamic arrays, use the UNIQUE function as explained in the above linked tutorial.
To avoid any confusion, first off, let's agree on what we call unique values in Excel. Unique values are the values that exist in a list only once. For example:
To extract a list of unique values in Excel, use one of the following formulas.
Array unique values formula (completed by pressing Ctrl + Shift + Enter):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)<>1), 0)), "")
Regular unique values formula (completed by pressing Enter):
=IFERROR(INDEX($A$2:$A$10, MATCH(0,INDEX(COUNTIF($B$1:B1, $A$2:$A$10)+(COUNTIF($A$2:$A$10, $A$2:$A$10)<>1),0,0), 0)), "")
In the above formulas, the following references are used:
Note. Because the formula references the cell above the first cell of the unique list, which is usually the column header (B1 in this example), make sure your header has a unique name that does not appear anywhere else in the column.
In this example, we are extracting unique names from column A (more precisely from range A2:A20), and the following screenshot demonstrates the array formula in action:
The detailed explanation of the formula's logic is provided in a separate section, and here's how to use the formula to extract unique values in your Excel worksheets:
As you may have already guessed from the heading of this section, distinct values in Excel are all different values in a list, i.e. unique values and first instances of duplicate values. For example:
To get a distinct list in Excel, use the following formulas.
Array distinct formula (requires pressing Ctrl + Shift + Enter):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")
Regular distinct formula:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")
Where:
If your source list contains any blank cells, the distinct formula we've just discussed would return a zero for each empty row, which might be a problem. To fix this, improve the formula a bit further:
Array formula to extract distinct values excluding blanks:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") + IF($A$2:$A$10="",1,0), 0)), "")
In a similar manner, you can get a list of distinct values excluding empty cells and cells with numbers:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), "")
As a quick reminder, in the above formulas, A2:A10 is the source list, and B1 is cell right above the first cell of the distinct list.
The following screenshot shows the result of both formulas:
When working with case-sensitive data such as passwords, user names or file names, you may need to get a list of case-sensitive distinct values. For this, use the following array formula, where A2:A10 is the source list, and B1 is the cell above the first cell of the distinct list:
Array formula to get case-sensitive distinct values (requires pressing Ctrl + Shift + Enter)
=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), "")
This section is written especially for those curious and thoughtful Excel users who not only want to know the formula but fully understand its nuts and bolts.
It goes without saying that the formulas to extract unique and distinct values in Excel are neither trivial nor straightforward. But having a closer look, you may notice that all the formulas are based on the same approach - using INDEX/MATCH in combination with COUNTIF, or COUNTIF + IF functions.
For our in-depth analysis, let's use the array formula that extracts a list of distinct values because all other formulas discussed in this tutorial are improvements or variations of this basic one:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")
For starters, let's cast away the obvious IFERROR function, which is used with a single purpose to eliminate #N/A errors when the number of cells where you've copied the formula exceeds the number of distinct values in the source list.
And now, let's break down the core part of our distinct formula:
In this example, COUNTIF($B$1:B1, $A$2:$A$10) returns an array of 1's and 0's based on whether any of the values of the source list ($A$2:$A$10) appears somewhere in the distinct list ($B$1:B1). If the value is found, the formula returns 1, otherwise - 0.
In particular, in cell B2, COUNTIF($B$1:B1, $A$2:$A$10) becomes:
COUNTIF("Distinct", {"Ronnie"; "David"; "Sally"; "Jeremy"; "Robert"; "David"; "Robert"; "Tom"; "Sally"})
and returns:
{0;0;0;0;0;0;0;0;0}
because none of the items of the source list (criteria) appears in the range where the function looks for a match. In this case, range ($B$1:B1) consists of a single item - "Distinct".
MATCH(lookup_value, lookup_array, [match_type])
returns the relative position of the lookup value in the array. MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)
turns into:
MATCH(0, {0;0;0;0;0;0;0;0;0},0)
and returns 1
because our MATCH function gets the first value that is exactly equal to the lookup value (as you remember, the lookup value is 0).
In this example, INDEX($A$2:$A$10, 1)
becomes:
INDEX({"Ronnie"; "David"; "Sally"; "Jeremy"; "Robert"; "David"; "Robert"; "Tom"; "Sally"}, 1)
and returns "Ronnie".
When the formula is copied down the column, the distinct list ($B$1:B1) expands because the second cell reference (B1) is a relative reference that changes according to the relative position of the cell where the formula moves.
So, when copied to cell B3, COUNTIF($B$1:B1, $A$2:$A$10) changes to COUNTIF($B$1:B2, $A$2:$A$10), and becomes:
COUNTIF({"Distinct";"Ronnie"}, {"Ronnie"; "David"; "Sally"; "Jeremy"; "Robert"; "David"; "Robert"; "Tom"; "Sally"}), 0)), "")
and returns:
{1;0;0;0;0;0;0;0;0}
because one "Ronnie" is found in range $B$1:B2.
And then, MATCH(0,{1;0;0;0;0;0;0;0;0},0) returns 2, because 2 is the relative position of the first 0 in the array.
And finally, INDEX($A$2:$A$10, 2)
returns the value from the 2nd row, which is "David".
Tip. For better understanding of the formula's logic, you can select different parts of the formula in the formula bar and press F9 to see what a selected part evaluates to:
If you still have difficulties figuring out the formula, you can check out the following tutorial for the detailed explanation of how the INDEX/MATCH liaison works: INDEX & MATCH as a better alternative to Excel VLOOKUP.
As already mentioned, the other formulas discussed in this tutorial are based on the same logic, with just a few modifications:
Unique values formula - contains one more COUNTIF function that excludes from the unique list all items that appear in the source list more than once: COUNTIF($A$2:$A$10, $A$2:$A$10)<>1
.
Distinct values formula ignoring blanks - here you add an IF function that prevents blank cells from being added to the distinct list: IF($A$2:$A$13="",1,0)
.
Distinct text values formula ignoring numbers - you use the ISTEXT function to check whether a value is text, and the IF function to dismiss all other value types, including blank cells: IF(ISTEXT($A$2:$A$13)=FALSE,1,0)
.
If you don't want to waste time on figuring out the arcane twists of the distinct value formulas, you can quickly get a list of distinct values by using the Advanced Filter. The detailed steps follow below.
Please pay attention that although the Advanced Filter's option is named "Unique records only", it extracts distinct values, i.e. unique values and 1st occurrences of duplicate values.
In the final part of this tutorial, let me show you our own solution to find and extract distinct and unique values in Excel sheets. This solution combines the versatility of Excel formulas and simplicity of the advanced filter. In addition, it provides a couple of unique features such as:
And now, let's see the Duplicate Remover tool in action.
Supposing you have a summary table created by consolidating data from several other tables. Obviously, that summary table contains a lot of duplicate rows and your task is to extract unique rows that appear in the table only once, or distinct rows including unique and 1st duplicate occurrences. Either way, with the Duplicate Remover add-in the job is done in 5 quick steps.
The Duplicate Remover wizard will run and select the entire table. So, just click Next to proceed to the next step.
In this example, we aim to extract unique rows that appear in the source table only once, so we select the Unique option:
Tip. As you can see in the above screenshot, there are also 2 options for duplicate values, just keep it in mind if you need to dedupe some other worksheet.
In this example, we want to find unique rows based on values in all 3 columns (Order number, First name and Last name), therefore we select all.
Because we are extracting unique rows, select Copy to another location, and then specify where exactly you want to copy them - active sheet (select the Custom location option, and specify the top cell of the destination range), new worksheet or new workbook.
In this example, let's opt for the new sheet:
Liked this quick and simple way to get a list of unique values or rows in Excel? If so, I encourage you to download an evaluation version below and give it a try. Duplicate Remover as well as all other time-saving tools that we have are included with Ultimate Suite for Excel.
Find Unique Values in Excel - sample workbook (.xlsx file)
Ultimate Suite - evaluation version (.exe file)
Table of contents