If you want to get a list of unique values in a column, this tutorial offers a number of formulas and will teach you how to tweak them 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. Continue reading
Comments on: How to get a list of unique and distinct values in Excel
by Svetlana Cheusheva, updated on
Comments page 3. Total comments: 171
Thank You!!
@Svetlana Cheusheva thanks a lot. it worked well for me.
Great formula to extract the unique values of any list. Well done, I would have not been able to figure it out how to do it on my own. Cheers!
hi there
could you please help me if possible
in sheet one I have
in column A the animals Gender
in column B the animals species
in column C the animals age
there are several other column's with weight, colour and other details
I have sorted my data by column B then by C
so if there are 15 cats at the top of the sheet followed by 11 dog and then 3 birds and then 14 rabbits
I would like to take the first 10 rows of each species and copy them into sheet 2
regards leigh
Hi there,
Could you please explain that why we are using, and what its meaning that using "zero" value either as match formula's "lookup value", and index formula's "row number" and "column number" ?
I am referring to this:
=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)), "")
Thank you.
Step 1: Use Google Sheets
Step 2: =unique(range)
Step 3: Profit
Hi,
I want to select unique entries from a range of cells. I tried this formula and it shows "0" as result.
My range is A3:F12 and i am trying to get the results starting in A15(formula cell). I used the following formula. Please assist.
=IFERROR(INDEX($A$3:$F$12,MATCH(0,COUNTIF($A$15:A15,$A$3:$F$12)+(COUNTIF($A$15:A15,$A$3:$F$12)1),0)),"")
Regards,
Sunny
Column A Column B Desired result
Class Code Stipend Class Code Amount Count
J7-288-001 1500 J7-288-001 1500 3
J7-288-001 1300 J7-288-001 1300 2
J7-288-001 1500
J7-288-001 1300
J7-288-001 1500
Hello!
Thanks for a great article. I read it with interest and built a list of unique names from the original list of duplicates using the array formula. My issue is that when I add more non-unique entries to the original list, the new list changes order. This is unhelpful as I use this new list in a table that has vlookup references.
Any ideas why the new list changes the order of the names?
Thanks!
Paul
Hi!
Is there a way to make the locked rows in the 'unique + 1st duplicate' example dependant on another column? That way I am able to do this procedure for multiple unique rows.
Thank you!
He genius
Anyone know to extract distinct value between 2 dates and 1 criterion. Criterion is either number or mixed. Example my unique value Column A, Date 1 Column B Date 2 Column & my creteria is column C. How to extract unique value in column A between 2 dates and base on criteria C?
I tried to get the "list of distinct text values ignoring numbers and blanks" to work in a sheet I was working on, to not avail. Then I copied the example and the formulas given and it would not work as shown. No error messages, just nothing in cells. The function result show the first instance in the dialog box, but not the cell, then nothing below the cell.
Thanks alot
Its so helpful
Hi Svetlana,
It's a great post; well explained. thanks.
A question for you:
Is it possible to use the INDEX formula for an Excel table? I converted your Excel data to a table (Table1) and used the following formula but it does not work:
=INDEX(Table1[[#Headers],[Product]], SMALL(IF(D$2=Table1[[#Headers],[Seller]],ROW(Table1[[#Headers],[Seller]])-MIN(ROW(Table1[[#Headers],[Seller]]))+1,""), ROW()-2))
However, VLOOKUP works but as expected it only gives the first occurrence of the matching field:
=VLOOKUP(D$2, Table1,2,FALSE)
Any ideas? Many thanks.
Abbas
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
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.