by 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.
How to get unique values in Excel
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:
- A2:A10 - the source list.
- B1 - the top cell of the unique list minus 1. In this example, we start the unique list in B2, and therefore we supply B1 to the formula (B2-1=B1). If your unique list begins, say, in cell C3, then change $B$1:B1 to $C$2:C2.
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:
- Tweak one of the formulas according to your dataset.
- Enter the formula in the first cell of the unique list (B2 in this example).
- If you are using the array formula, press Ctrl + Shift + Enter. If you've opted for the regular formula, press the Enter key as usual.
- Copy the formula down as far as needed by dragging the fill handle. Since both unique values formulas are we encapsulated in the IFERROR function, you can copy the formula up to the end of your table, and it won't clutter your data with any errors no matter how few unique values have been extracted.
How to get distinct values in Excel (unique + 1st duplicate occurrences)
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:
- A2:A10 is the source list.
- B1 is the cell above the first cell of the distinct list. In this example, the distinct list begins in cell B2 (it's the first cell where you enter the formula), so you reference B1.
Extract distinct values in a column ignoring blank cells
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)), "")
Get a list of distinct text values ignoring numbers and blanks
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:
How to extract case-sensitive distinct values in Excel
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)), "")
How the unique / distinct formula works
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:
- COUNTIF(range, criteria) returns the number of cells within a range that meet a specified condition.
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.
In this example, the lookup_value is 0, and consequently: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).
- INDEX(array, row_num, [column_num]) returns a value in an array based on the specified row and (optionally) column numbers.
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).
Extract distinct values from a column with Excel's Advanced Filter
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.
- Select the column of data from which you want to extract distinct values.
- Switch to the Data tab > Sort & Filter group, and click the Advanced button:
- In the Advanced Filter dialog box, select the following options:
- Check Copy to another location radio button.
- In the List range box, verify that the source range is displayed correctly.
- In the Copy to box, enter the topmost cell of the destination range. Please keep in mind that you can copy the filtered data only to the active sheet.
- Select the Unique records only
- Finally, click the OK button and check the result:
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.
Extract unique and distinct rows with Duplicate Remover
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:
- Find and extract unique / distinct rows based on values in one or more columns.
- Find, highlight, and copy unique values to any other location, in the same or different workbook.
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.
- Select any cell within your source table and click the Duplicate Remover button on the Ablebits Data tab, in the Dedupe group.
The Duplicate Remover wizard will run and select the entire table. So, just click Next to proceed to the next step.
- Select the value type you want to find, and click Next:
- Unique
- Unique +1st occurrences (distinct)
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.
- Select one or more columns to be checked for unique values.
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.
- Choose the action to perform on the found unique values. The following options are available to you:
- Highlight unique values
- Select unique values
- Identify in a status column
- Copy to another location
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:
- Click the Finish button, and you are done!
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.
Available downloads
Find Unique Values in Excel - sample workbook (.xlsx file)
Ultimate Suite - evaluation version (.exe file)
171 comments
Hello,
Please could anyone help with this?
I have a large catalogue (30k rows) with partial repetitions and I need to find the unique segments, for example:
Column A
Shoes
Shoes for men
Shoes for men size 8
Shoes for men size 10
Shoes for men size 12
Shoes for men size 14
Shoes for women
Shoes for women size 8
Shoes for women size 10
Shoes for women size 12
Shoes for women size 14
Jackets
Jackets ABCAC
Jackets for men DAXX
Leather jackets for men REV0
Jackets for women ABCC
Jackets for women AABBCCDD
The unique list I need to find would be:
Shoes
Shoes for men
Shoes for men size
Jackets
Jackets for men
Leather jackets for men
Jackets for women
All codes, sizes, etc can be ignored.
Thank you so much.
Marcos
Hi
I've been using this formula to extract values only from columns in my front sheet "FrontPage" but I need to extract values greater than 60. which this doesn't do.
=IFERROR(INDEX(FrontPage!E:E,SMALL(INDEX(ISNUMBER(FrontPage!E$7:E128)*ROW(FrontPage!E$7:E128),0,),COUNTBLANK(FrontPage!E$7:E128)+ROW(FrontPage!E3))),"")
I've tried this array formula but it causes circular references due to the row ref
{=IFERROR(INDEX(FrontPage!F$1:F$2000,SMALL(IF(FrontPage!F$1:F$2000>65,ROW($1:$2000)),ROW())),"")}
Your help would be much appreciated
Colin
Is it possible to get the data from the following table in excel:
Game Player Goals
----- ------ ------
Game1 John 1
Game1 Paul 0
Game1 Mark 2
Game1 Luke 1
Game2 John 3
Game2 Paul 1
Game2 Luke 1
Game3 John 0
Game3 Mark 2
which gives a result like this:
Game John Paul Mark Luke
----- ---- ---- ---- ----
Game1 1 0 2 1
Game2 3 1 - 1
Game3 0 - 2 -
kindly help with excel formula
Hi Svetlana,
I am trying to pull data from one column, based off of data from another column, but return both values. I may have missed it because I am relatively novice in terms of Excel but I've been experimenting. Here's an example Data Set:
Column A Column B
First 1
Second 2
Third
Fourth
Fifth 5
Sixth
Seventh
Eighth 8
I would like it to return:
Column C Column D
First 1
Second 2
Fifth 5
Eighth 8
Is this something that is possible? Would I have to incorporate a Pivot Table or have to code it in Visual Basic? Basically, it's a function that would be able to take a large data set and condense it down. Something where Column A would have titles/values from Line 2 to 100. Columns B through Z would only have 5 or 6 values entered down the column, though. The application is for Wall Panel manufacturing where Column A is the part name (~100 part names) but each Panel only uses 5 or 6 parts; WP1 might use parts 3, 6, 7, 8 but WP2 might use parts 10, 19, 25, 67 so i'd like to condense it down panel by panel.
Hopefully that makes sense...
how this part is working =COUNTIF($A$2:$A$10, $A$2:$A$10)
& how this is different from this =COUNTIF($A$2:$A$10,A2)1 since both has same result separately but in formula second option isn't working,please also explain this part more =COUNTIF($J$1:J1, $A$2:$A$10)...??thnx
this one is correct, =COUNTIF($A$2:$A$10,A2) wrongly place 1 at the end.
Nicely done. Thank you!
I need to get the list of duplicates in Column B and it should return its value and the value in Column A.
Hello,
I have two Worksheets---WS1 Customer column A(match)& Customer Number B(return value) ....WS2 Customer column E ...WS 2 Column Q (formula)- I need a formula that will match WS2 Column E to Column A WS1 and return value of column B WS1
Thank you in advance!!
To add the columns in WS 1 Customer are not alphabetized. So, therefore other index match formulas I am using, are not working.
Hello Expert
Is there a way to list distint text value with criteria? Example in the following
G2:G20 - Emloyee (Text & Number)
E2:E20 - Work Week (number only)
is there a way for me employee name in work week 30 to list like below
WW=30 (Criteria)
Employee
CU110
1267
NP230
27786
Your explanations and examples are very impressive. One issue that confused me was how to propagate the array formulas. I found I had to enter the formulas and propagate them before making them arrays. I then had to individually make the formula of each cell an array with CTRL+SHIRt+ENTER.
Did I miss something in the examples explanations?
I am also a programmer so I may tend to use VBA when the formulas become too subtle. Maybe your readers may want to consider this as well. The macro recording feature may be another approach as well.
Best regards.
So I'm trying to utilize the formula function to get it to essentially do it automatically but every time I put in the formula it always kicks back telling me the Cells are empty do I still have to copy and paste in the values? or is there some way that I can have it pull the values from the cells and only display the unique values on its own?
Hi Svetlana,
I am able to extract unique list as per your instructions. Thanks.
Could you please help me sort the unique list that is generated?
Hi Ravi,
You will have to replace formulas with values first (Paste Special > Values), and then you can use the Excel Sort option (Data tab > Sort & Filter group, A-Z button).
this doesn't work, I get banana for every output, why is this?
Hi Andy,
What formula are you using?
Hello!
Thank you so much for explaining how to do this. I have two questions.
Currently, the range I'm using as my source is a named column in a named table in a separate sheet. I've had no problems referring to the source list in the "distinct values ignoring blanks" formula, using the format TableName[Column] in place of each instance of $A$1:$A$20 in the example. However, what I am wanting to do is use this formula in a validation rule on another sheet, so that I can have an automatically populated dropdown list based on TableName[Column]. Of course, simply doing =INDIRECT(TableName[Column]) returns the entire list, which is full of blanks and duplicates, but removing the IFERROR portions of the formula and trying to incorporate INDIRECT(TableName[Column]) does not seem to be working either. Is there anything I can do to modify the formula to achieve my goal?
If what I described above is not possible, I've thought of an alternative, but that brings me to my second question. If I were to use the "distinct values ignoring blanks" formula as a formula in Sheet 2, define the extracted list of distinct values from TableName[Column] as TableName2[Column1], and then use =INDIRECT(TableName2[Column1]) as a validation rule for Sheet 3, would there be any way to have TableName2[Column1] automatically update/add to itself...? The thing is, I know I will be adding more values to TableName[Column] from Sheet 1 and I'm hoping I can have an up-to-date list of all of its distinct values available to me at any given time. I really have no estimate of just how many values that would be. By any chance, is it possible for the formula to copy itself to the adequate number of cells when a change is detected in the range it depends on, ideally in a way that does not require me to manually update the reference of the validation rule every time? Or something similar to that, maybe? Ah, I hope that makes sense.
Thank you in advance!
hi,
How can i modify the above formulas in the article to add a second criteria on the same range that i want to extract ,
ex: the range is numbers like , 50001, 50002, 50003, 100001,20001,
i want to extract only the number that start with 5000 but unique with no duplication.
Thanks in advance.
Hello,
I have a large list of names from a survey where the names were inputted differently each time. Many issues involved differences of case sensitivity as well as additional spaces. I have used an =UPPER(TRIM(x)) function to remedy these issues, but I cannot reference the resulting cells for the advanced filter function to extract unique names. Is there a way to remedy this problem?
Additionally some problematic cells contain issues like this:
Smith JosephJohn
Smith Joseph John
Is there a way to identify and fix these cells faster than manually looking at each one?
Thank you
Mischa:
Sometimes the condition of the data requires you to do what you can with the help of the software and then there is nothing to do except go through the data line by line and clean it up.
How to find unique transaction count
For example
1 Apr 3018 there is so many sales, I have to count to how many customer we sold the product ( unique customer) based on the date of Sales
Hi,
I've used your formula and works just fine.
From 1000 rows where only 337 are filled in with information, I get 62 distinct values.
However, there's a big problem. I got those 337 lines in a separate Excel sheet just to verify and after I have selected "Remove Duplicates" manually, I saw that I was supposed to get 65 values.
The 3 values (cells) that are NOT present have the longest character count (295,319 and 359 characters).
Is there a limitation? Why is it not retrieving these 3 lines?
I'm using the correct formula:
{=IFERROR(INDEX($A$2:$A$1000, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$1000), 0)), "")}
Thank you,
Horatiu
How to create a unique & sorted distinct list where other columns meet Multi criteria?
Cat1 Cat2 List
BB A 4
AA B 2
AA A 1
BB C 2
CC A 2
BB B 4
BB A 2
BB B 3
CC C 1
CC A 5
BB A 3
BB B 4
AA A 2
BB B 5
BB A 4
Criteria in Cat1: BB or CC
Criteria in Cat2: A
Result:
Created List
2
3
4
5
Hi Svetlana,
I'm trying to get all distinct values in a secondary sheet but I need the formula to also have 2 conditions in it, as I have a huge list with at least 6 prices for several venues.
I've been trying on my own but couldn't figure it out yet, seems index&match formulas are giving me bit of a headache.
Can you give an example of how conditions could be included in it?
Thanks