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
Hi Svetlana, thanks for this!
Hi, about the Extract distinct values ignoring blanks, is there a way to expand this formula (without neeeding to use vba, pivot etc) to include checking for one or more criteria in other columns, before returning the names. I uploaded the workbook. In it, there are 2 resulting tables, each showing distinct names. 'Month' column is for reference only, please ignore it. Non-array formula, if possible, will be best.
https://drive.google.com/open?id=1l7mGduN0lzaSjLXzkY40H_gzMAC_gqUx
Thank you!
Hi Eddie,
To my best knowledge, it can only be done with an array formula, and I have added it to your workbook. However, the Active list returned by the formula is different from your resulting table because a few names are marked as both Active and Non-active.
Hi Svetlana, thanks for your reply. I realized I had previously set the file in gdrive to be read-only. Do you mind sending the updated workbook to my email. Thanks again!
Eddie,
I've uploaded it to our web-site in case someone else is looking for a similar solution. You can download the workbook here (formula cells are highlighted in blue).
hi
thats nice
Dear Svetlana,
What if I want to add a second filter to an array formula? Problem occurs with the expanding part of the formula, let's assume I have the following Data:
Dimension Name
0.80 Sur
0.80 Sur
0.80 Sur
0.80 Sur
0.80 Sur
0.80 Sur
0.80 Sur
1.30x0.70 Sur
1.60x0.70 Sur
0.80 Sur
0.90 Sur
0.80x0.36 EM4
0.80x0.36 EMC1
0.90x0.36 EMC1
1.00x0.36 EMC1
0.60 Canto
0.80 Canto
1.00x0.75 Canto
1.20x0.75 Canto
1.20x0.75 Canto
1.20x0.75 Canto
1.00x0.76 Canto
What I want to obtain is a list of unique values of dimensions per Name, that is to say, the unique dimension values that share a same name. Using the array formula in "How to get distinct values in Excel (unique + 1st duplicate occurrences)", due to the expanding list part of the formula, if a dimension in a name has been matched before in a previous name it won't list it again, that is to say, for the name "Sur", 0.80 will list as a unique value, however for the Name "Canto", 0.80 won't list since it was already listed on "Sur". How could I modify the formula so that it creates a new expanding list each time a new Name appears on the list?
Cheers and thank you.
Hello,
If we suppose that your table starts with cell A1, please try to do the following:
1. Enter the formula below in cell C2:
=IFERROR(INDEX($A$2:$A$23, MATCH(0, INDEX(COUNTIFS($C$1:C1, $A$2:$A$23,$D$1:D1, B$2:$B$23), 0, 0), 0)), "")
2. Enter this formula in cell D2:
=IFERROR(INDEX($B$2:$B$23, MATCH(0, INDEX(COUNTIFS($C$1:C1, $A$2:$A$23,$D$1:D1, B$2:$B$23), 0, 0), 0)), "")
3. Select the cells where you've entered the formulas and copy them down to the other cells in the columns by dragging the fill handle (a small square at the lower right-hand corner of the selected cell).
Hope this will help you with your task.
Hi Svetlana,
Good day. Thank you for the article. It really helped me a lot.
I just have one problem here in my excel sheet. I get that in your examples, the list exist only on one column. What if my list consist of an array of cells. Please refer to the table below. I have 2 columns of data and I want to make a list of all the data. I hope you could help. Thanks!
DATA 1 DATA 2 RESULT
A1 B1 A1
A2 B1 A2
A2 B1 A3
A3 B2 B1
A3 B2 B2
A3 B2
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Hi Svetlana,
what is the role of &"" after a range in countif formula? When I evaluate the formula I can see the difference but I would like to understand how it works.
Hi Svetlana,
Instead of finding unique names in a column, I need to find unique names in multiple columns. Is there an easy way?
Thank you for the info.
I would like to find out, for "Extract distinct values in a column ignoring blank cells", how can I sort the list by alphabetical order.
regards.
Hello, Wendy,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Thank you for the information.
I would like to know how to "Extract distinct values in a column ignoring blank cells" and also arrange them in alphabetically order.
Many thanks in advance!
Hi, thank you for your instructions and help!
I'm wondering if you know of a way to return multiple corresponding values from more than one column (moving on to the next column if a cell is blank) for one Lookup Value?
Hi Mia,
You can use nested IFERROR functions to do sequential Vlookups as shown in this example.
Hey -- I tried this on excel 2016 and it isn't working because COUNTIF isn't returning an array - simply one value which doesn't correspond to an index in MATCH. Any ideas on why this is happening in my case?
What is limitation for range?
500 rows is ok
1000 rows is NOT OK (shows doubles)
I have a large spreadsheet. Serial numbers are in Column "A"; Warranty Expiration in Column "E". Serial numbers in Column A may have duplicates.
For example; Serial Number 12345 may appear in cell A2 and A7.
Column E may be blank (if no warranty exists); or may have two different dates (12/1/2015 in E2 and 12/1/2019 in E7 for example).
I need the furthest out warranty expiration date from Column E per unique Serial Number (Col A) - basically consolidate duplicate serial numbers and return a unique warranty end date.
Ultimately I would see Serial Number 12345 in A2 and 12/1/2019 in E2.
How do I do that?
Hello, Gina,
I can suggest you to take a look at the following article about data consolidation in Excel. If we understand your task correctly, it may help you with your task.
If it won't help, I'm afraid you will need some kind of a macro. Since we don't help with those, please ask around Mr. Excel forum for it.
I really hope you'll manage to solve the task!
I have a 19+ digit variant number always starting with 6................-... The "-" is always on number 19 slot after which a digit or two or three or four digits follows. These unique numbers are always exist once within column H to X in each row of data. Sometimes, i could have 60 thousand row from which to extract this value into one specific column. Definitely, l need formular help to achieve this task. Can you help me please with something that will do this and still give me the result as editable values?
Please help!!!
I want to extract the list(data validation) from the data as below. First lookup the PO# and then create a list of corresponding distinct values.
PO# Category
KIPS/IT/01 CPU
KIPS/IT/01 LCD
KIPS/IT/01 LCD
KIPS/IT/02 Cable
KIPS/IT/02 CPU
KIPS/IT/02 LCD
KIPS/IT/03 Connector
KIPS/IT/04 CPU
KIPS/IT/04 LCD
KIPS/IT/04 Cable
KIPS/IT/04 Connector
KIPS/IT/04 Mouse
KIPS/IT/05 CPU
KIPS/IT/05 LCD
KIPS/IT/05 Cable
KIPS/IT/06 CPU
KIPS/IT/06 LCD
KIPS/IT/06 Cable
KIPS/IT/06 Connector
KIPS/IT/06 Mouse
KIPS/IT/07 Webcam
KIPS/IT/07 Toner
KIPS/IT/07 Miscellaneous
Hi,
Is there a way out to extract/retrieve every nth value from a filtered data range?
Thanks in advance :)
Yes.it is possible.
share some sample data to understand your query properly.
Hey,Thanks for your help...
I have a query on the same..I have been trying some expansion to this formula but no luck. I need to use this formula with couple of conditions, Such as date range from Jan to Mar or Apr to Jun or Jan to May, something like that..Appreciate your help.. thanks in advance
Value of: Employee=500 & Relative=700
How do I get respective value 500/700 in B1 when I write Employee/Relative in A1
Please help.
Dear Svetlana,
I need to extract a corresponding value from say column i and j for a value from column b, and my 2nd number would be 'previous value' in column 'b' plus constant c and there corresponding value from columns i and j.
b c d e f g
6.950323 506.995 506.995 0.235604169 46.54370561 0.211560057
57.50959672 0.187815384 56.3904946
b+c
7.149519 508.124 508.124 0.242356576 46.6473513 0.217010041 57.95264365 0.193082442 56.7702949
this continues for entire data, with value of 'c' being constant getting added to value from column b, like equally spaced...
Could you please help me out on this.
With Best regards
Rajesh KC
Hello..
i have data like below.
sid dose date
101 a 11-Jan-2017
101 a 13-Jan-2017
101 a 9-Jan-2017
101 b 12-Jan-2017
102 a 11-Jan-2017
102 a 1-Jan-2017
I want data like: I want unique subject, and unique treatment and minimum date in unique treatment.
Can someone let me know how can u do that..
Thank you
Satheesh K