How to get a list of unique and distinct values in Excel

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.

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:

1. 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".

2. `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).

3. 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.

1. Select the column of data from which you want to extract distinct values.
2. Switch to the Data tab > Sort & Filter group, and click the Advanced button:
3. 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

4. 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.

1. 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.

2. 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.
3. 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.

4. 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:

5. 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.

Find Unique Values in Excel - sample workbook (.xlsx file)
Ultimate Suite - evaluation version (.zip file)

You may also be interested in

142 comments to "How to get a list of unique and distinct values in Excel"

1. DSM says:

IN my case I've got a sheet containing a list of appointments between sales people and clients, and I'm trying to count the number of clients each sales person has. To this end, I've used your list distinct values formula to create a list in a column of each sales person, but in a row beside those, I'm trying to create a horizontal list of all distinct clients that sales person has seen. How would I adapt the formula to generate that list of clients?
Thanks for any help!

• Hello!
You can find the examples and detailed instructions here: Get a list of unique values based on criteria.
To write the result in one cell, you can use the TEXTJOIN function:

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(A2:A10,C2:C10=F1),FALSE))

2. Kevin says:

The distinct value formula is spot on. But is there a way to keep those distinct values within the row its mimicking?
For example
Apples was in row 15 and the formula moves apples to row 1. I need apples to stay in row 15 but still identified as a "distinct" value.

3. SP says:

Hi
Many thanks for this article. I have data like the below in sheet TZ. In another sheet, I am listing the unique dates under column J.

Date | Amount | CODE
(column B)
------- ----------- ---------
15-JUL-2021 25000 HIN
15-JUL-2021 22000 HIN
13-JUL-2021 42000 TIN
12-JUL-2021 37000 HIN
07-JUL-2021 26000 PIN
06-JUL-2021 14000 HIN

From this list, I want to extract distinct values of date corresponding to a code HIN alone.

=IFERROR(INDEX(TZ!\$B\$2:TZ!\$B\$1746, MATCH(0, IF(ISBLANK(TZ!\$B\$2:TZ!\$B\$1746),1,COUNTIFS(\$J\$1:J1, TZ!\$B\$2:TZ!\$B\$1746) ), 0)), "")

4. Mark says:

Hi

I have a list of historical orders as follows

Harry Skirt1
Harry Skirt2
Harry Skirt2
Harry Tie1
Harry Tie1
Harry Skirt1
Tony Trouser1
Tony Skirt1
Tony Trouser1
Tony Skirt1

What I want to be able to do is type customer name into a box and list the products they took (removing duplications)

So in a cell I type (or choose from a list) Harry

That gives me:
Skirt1
Skirt2
Tie1

How do I do that - I have a lot of data as its listed by SKU (style / colour / size) so the formula in the instructions took a long time to load up

Appreciate any help guys

5. Tom says:

Hi Everyone,

I'm having trouble that the unique function is still returning duplicates for me. I've tried both the UNIQUE function and other code using the COUNTIF function to do the same thing - same results. I assume the problem is somehow related to formatting. Here's what I'm trying to do:

I'm breaking a list of numbers in column F into:
(Column G) # Multiples of a certain number (in G2): =ROUNDDOWN(F7/\$G\$2,0)
(Column H) # The remainder: =F7-\$G\$2*G7

I then want to find all the unique values from the remainder column H (this is column J), but it's returning multiples of some numbers. Note that my data starts at row 7, hence G2 is unrelated to the # Multiples data.

I've narrowed it down a little to a concise example:

G2 = 3.6

F G H J

3.4 0 3.4 3.4
25 6 3.4 3.4

Why is the unique function not excluding the second instance of 3.4 in column H?

• Hello!
What formula are you using in column J? In what range do you want to find unique values?

6. Enrique says:

Hello,

When I do the regular distinct formula:
=IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, INDEX(COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0, 0), 0)), "")

Excel gives me 0 but when I click Ctrl + F9, It gives me :
{"Aportes"}
Aportes is the unique value I would like to see but it gives 0.

Can someone help me?

• Hi,
I have not found an error in this formula. Without seeing your data it is difficult to give you any advice. It works for me correctly. You may not have entered it as an array formula.

7. ajit says:

Looking for a formula to fetch results as below
Source Data:

Sub roll no marks
maths 1 8
Eng 2 9
maths 3 7
Eng 4 4
Eng 1 5
maths 2 3
maths 4 7
Eng 3 6

Results Needed:

Roll no Maths Eng
1 8 5
2 3 9
3 7 6
4 7 4

8. Abhi says:

Hi Am looking for the below formula

A=1
B=2 like wise i have so many data.

My required is AB = 12 and BA=21 how to put formula for this?

A 1 A 1
B 2 BG 27
C 3
D 4
E 5
F 6
G 7
H 8
I 9

9. Mike says:

Thanks for the quick replay Alexander!

When trying that array formula including entering with Ctrl + Shift + Enter, it pops up an error message window saying, "That function isn't valid." After hitting "OK" to acknowledge the error, it highlights the word "FILTER" in the formula. Perhaps the syntax is not correct...? Any ideas?

Thanks for your help to resolve!

10. Mike says:

Hi,

How do I modify the formula below if I have another criteria column:

=IFERROR(INDEX(\$A\$8:\$A\$8, MATCH(0, INDEX(COUNTIF(\$C\$1:C1, \$A\$2:\$A\$8), 0, 0), 0)), "")

For example:

A1 B1
Pete C
Betty C
Frank C
Mary O
Frank O
Mary O
Suzy O

I want the formula to look only at rows with "O" and return distinct names.

Result:

C1
Mary
Frank
Suzy

Your help is much appreciated! Thank you!

• Hello!

=IFERROR(INDEX(FILTER(A2:A10,B2:B10="O"), MATCH(0, COUNTIF(\$C\$1:C1, FILTER(A2:A10,B2:B10="O")), 0)), "")

This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

11. Justin says:

Great Article-

I have a follow-up question I cannot figure out. I want to create a list of unique values from another tab that dynamically updates, only I want the new values to always paste/fill in at the bottom of the unique value list. When I change the data source by copying in new data, sometimes new unique values appear in the middle or top of the data set and then these values appear in the middle of my output list. I cannot have this occur as I need the list values to stay in the same rows so that they are matched with some manually input information that goes with them, rather than moving around without the accompanying values in other columns moving with them.

12. Arun says:

Hi,
How to How to get distinct values in Excel (unique + 1st duplicate occurrences), based in a cell reference
for example
Column A Column B
A Apple
B Banana
A pineapple
C Orange
A Apple

I want a list of distinct values based in column A i.e if A, then
Apple
Pine Apple
if B
Banana

• Hello!
Study the section in this article, "How to get distinct values in Excel." If you have specific questions after that, I will try to answer them.

13. Ape says:

Hi
I am trying to do a lookup from one sheet within multiple rows and columns. Customer, Part number will have different qty breaks with different prices.Rows have duplicate part numbers and customer, possible price.
Thanks
How would you recommend the easiest way?

14. Ape says:

Hi
I am trying to do a lookup from one sheet within multiple rows and columns.
Customer, Part number will have different qty breaks with different prices.
Rows have duplicate part numbers and customer, possible price.
Thanks

How would you recommend the easiest way?

15. Random Sparks says:

I have two sheets
First sheet has 470 records, some duplicates.
Second sheet has 1000 records, some duplicates.
I want to find all unique values on sheet one that are NOT on sheet two.
So, any value found in sheet two that matches on sheet one will not be shown. I only want unique values from sheet one. If there are duplicates within sheet one that are not on sheet two they should remain after filtering.

16. Keshav says:

Can I get a formula to remove duplicates and blanks when the data is displayed in a row. I tried to make some changes in the formula which is mentioned for data placed in a column, but couldn't get the desired results.

• Hello Keshav!
If the list of values is on the line (for example, A2:P2), then the list of unique values can be obtained using the formula in cell S2.
Regular unique values formula (completed by pressing Enter):

=IFERROR(INDEX(\$A\$2:\$P\$2, MATCH(0,INDEX(COUNTIF(\$S\$1:S1, \$A\$2:\$P\$2) + (COUNTIF(\$A\$2:\$P\$2, \$A\$2:\$P\$2)<>1),0,0), 0)), "")

In cell U2, we write the formula of the array (completed by pressing Ctrl + Shift + Enter):

=IFERROR(INDEX(\$A\$2:\$P\$2, MATCH(0, COUNTIF(\$U\$1:U1,\$A\$2:\$P\$2) + (COUNTIF(\$A\$2:\$P\$2, \$A\$2:\$P\$2)<>1), 0)), "")

After that you can copy this formula down along the column.

17. KR says:

Regarding the formula +IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0)), ""), I only getting the first value as it takes B1 and does not update to b2 b3 and so on when I use it. The array is not working.

• Hello,
You write this formula in cell B2.This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. You have not done so. After that you can copy this formula down along the column.

18. Makhan says:

Hello,
Great piece of knowledge for a novice like me.
2. By applying the formula =IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, INDEX(COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0, 0), 0)), "") the result is in the same order as was in original column, (a)It should come out sorted alphabetically (b)with other data in the row. Please provide formula for this.
It will be great help. God Bless You.

19. Cal says:

=IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, INDEX(COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0, 0), 0)), "") works but one needs to figure out how many distinct values are going to exist in order to copy and paste the formula that many times. Hence, if there are 10 distinct values then one copies and paste the formula 9 times. Is there a way for one does not need to know how many distinct values are going to exist?

Thank you

• Hello!
To calculate the number of unique values in a data range, use the formula

=SUMPRODUCT(COUNTIF(A2:A20,A2:A20)^(2*ISBLANK(A2:A20)-1))

I hope this will help, otherwise please do not hesitate to contact me anytime.

20. AB says:

I have a list of employees and each has a unique job category. Based on their job category, I want a unique list of job competencies to show up to select from a pull down list that are only available for that job category.

• Hello!
This link has detailed instructions on our blog.

21. LK says:

Hi, Thanks for this wonderful tutorial. I am having one issue with dates. I am trying to find unique dates but for blanks it is showing 00/01/1900 which is not desirable. can you suggest how to remove this.

22. Matthew says:

I have a list of employees with their work dates in a given year. I want to insert which period they fall in: a date from 15th to 14th of following month. So if the date is 01/05/2020 it should fall in the period 12/15/2019-01/14/2020. What formula would do this?
Employee Work Date Hours PERIOD
John 01/05/2020 2 12/15/2019-01/14/2020
John 01/15/2020 2 01/15/2020-02/14/2020
John 02/05/2020 2 01/15/2020-02/14/2020
John 03/06/2020 2 02/15/2020-03/14/2020

23. Irfan Basharat says:

Hi,
Is any way to auto up-date next Tab with specified category in ascending order in a row addition/filling data......!
If in Sheet1, we add a Row with distinct Name 'A" or "B" in column,should auto up-date in next/assigned Tab with same Name "A" or "B" like VLOOKUP but if I apply VLOOKUP, that collect /repeat 1st value.
Example:-
Sheet-1
Team PO No Date Status
A Akbar 3-Aug-19 Close
A Babar 3-Aug-19 Close
A Masood 3-Aug-19 Close
A Zara 3-Aug-19 Close
B Mehmood 18-Jan-19 Close
B Ali 25-May-19 Close
B Hina 17-Oct-19 Running

New Tab
Team PO No Date Status
B Mehmood 18-Jan-19 Close
A Akbar 3-Aug-19 Close
A Akbar 3-Aug-19 Close
A Akbar 3-Aug-19 Close
B Mehmood 18-Jan-19 Close
B Mehmood 18-Jan-19 Close
A Akbar 3-Aug-19 Close
Thanks

Is it possible to extra distinct collumned data across a row?
If so can you please share a simple formula to use?

25. Carlo says:

Is there a way to add a criteria on this formula? I want to execute this formula if it meets a certain criteria from a column? e.g
=IF(A:A="Criteria",(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)), "")

=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)), "")

26. Vivek says:

How generate list from range in for next column like
123456789101 to 123456789104
123456789108 to 123456789111
The results should be like
123456789101
123456789102
123456789103
123456789104
123456789108
123456789109
123456789110
123456789111

27. JeteMc says:

Thank You!!

28. Supun says:

@Svetlana Cheusheva thanks a lot. it worked well for me.

29. Julio Melchor says:

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!

30. leigh kimbrell says:

hi there
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

31. ozgu says:

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.

32. Andrew says:

Step 2: =unique(range)
Step 3: Profit

33. Sunny Dhillon says:

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

34. Zeshan says:

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

35. Paul says:

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

36. Bram says:

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!

37. Desmond LEE says:

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?

38. Bob says:

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.

39. Muhammed Naveed says:

Thanks alot

40. Abbas says:

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:
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

41. Marcos says:

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

42. Colin says:

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

43. prashant says:

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

44. Loren says:

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...

45. Nabeel says:

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

• Nabeel says:

this one is correct, =COUNTIF(\$A\$2:\$A\$10,A2) wrongly place 1 at the end.

46. Demetri says:

Nicely done. Thank you!

47. Ly says:

I need to get the list of duplicates in Column B and it should return its value and the value in Column A.

48. ag says:

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

• ag says:

To add the columns in WS 1 Customer are not alphabetized. So, therefore other index match formulas I am using, are not working.

49. Desmond says:

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

50. Dan McCormick says:

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.

51. Pete says:

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?

52. Ravi K says:

Hi Svetlana,

I am able to extract unique list as per your instructions. Thanks.

• 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).

53. andy says:

this doesn't work, I get banana for every output, why is this?

54. Calvin says:

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.

55. ZAYAT says:

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.

56. Mischa says:

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

• Doug says:

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.

57. Krishnaraja says:

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

58. Horatiu says:

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

60. T. says:

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

61. Eddie says:

Hi Svetlana, thanks for this!

62. Eddie says:

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.

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.

• Eddie says:

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).

63. valter says:

hi
thats nice

64. Eduardo says:

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).

65. Jonel Battung says:

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.

66. Lukas says:

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.

67. June8 says:

Hi Svetlana,

Instead of finding unique names in a column, I need to find unique names in multiple columns. Is there an easy way?

68. wendy says:

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.

69. wendy says:

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.

70. Mia says:

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?

71. Connor says:

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?

72. Mirko says:

What is limitation for range?

500 rows is ok

1000 rows is NOT OK (shows doubles)

73. Gina O says:

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,

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!

74. Kayode says:

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?

75. baqir ali says:

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

76. MEGHA PANDEY says:

Hi,
Is there a way out to extract/retrieve every nth value from a filtered data range?

• Peshiyaboy says:

Yes.it is possible.
share some sample data to understand your query properly.

77. Gulshan Mathur says:

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

78. Apurba says:

Value of: Employee=500 & Relative=700

How do I get respective value 500/700 in B1 when I write Employee/Relative in A1

79. rajesh says:

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...

With Best regards
Rajesh KC

80. satheesh says:

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

81. Ronoy says:

I have a problem. I want to find and count duplicate values in two ranges. Suppose I hv two sheets called A & B, there are both similar & common values in both A:A range in A sheet & A:A range in B sheet. In both separate sheets A & B, if one common value matches/finds/exacts/duplicates then count otherwise dont. Anyone have some suggestions how to do it????

82. Francis says:

I have a table that contains multiple values in column B for their corresponding dates in column A. For each date, i want to extract the values that appear only once and leave out the values that appear more than once. How do I do this please?

83. Rishabh says:

Hi..
Example
My unique value is : 120450
and in other sheet the unique value is with some other text for example: Rishabh 120450

it is a huge list and in each line there is something other than Rishabh, but the unique value is there everytime with a space.
I cant use Text to column to remove space as all the cell have different no of space.

Pls suggest can we use Vlookup to find 120450 in the other sheet?

Rishabh

84. Piotr says:

Dear Svetlana,
I have a issue coming up with formula for my unique list of Issues (Issue)column and attached to it sub category (Issue Details). I have managed to come up with Unique list of Issue details but some of it fall for main Cat Issue twice. For example: Issue Detail "procedure breach" may fall in 2 types of Issues "Policy" and "communication". Is there any way that you could direct me to the formula that could deal with such a dependency ? Or give me a hint how this issue could be resolved ?

85. Sri says:

Hi All,

I need Unique (Distinct) count of project (only active & approved)
By Month wise

Project Status Date
15746 active 1-Jan-16
15780 active 20-Jan-16
15795 active 5-Jan-16
15822 active 21-Jan-16
15822 active 22-Jan-16
15822 Closed 2-Jan-16
15780 Approve 2-Feb-16
15822 active 22-Feb-16
Answer: for Jan month:4 unique count(only active and approved Supplier)
Can anyone help me out on this!!

Sri

86. Shahbaz says:

Hi Svetlana Cheusheva,
i have a lot of data like this in one column and want to sort it for further working.
113-00-00-00-0000-10121-5109-00000
113-00-00-00-0000-10121-5110-00000
113-00-00-00-0000-10121-5151-00000
113-00-00-00-0000-10405-1114-00000
113-00-00-00-0000-10437-1112-00000
113-00-00-00-0000-10441-1112-00000
i want to create a formula to select all cells in other sheet which have suppose "10121".

87. Gopinath says:

Thank you , =IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0, INDEX(COUNTIF(\$B\$1:B1, \$A\$2:\$A\$10), 0, 0), 0)), "") did work now for me.

88. Srinath says:

I have data

001 - Xyz
001 - ABC
001 - DEF
002 - MNO

I want all the values of 001 in one cell. Please advise

89. Arvind says:

Hi All,

I have a question. Will i be able to aggregate non numeric data based on one just one of the columns in excel. I want all aggregation at this level. I know this is possible if there are 2 columns through pivot. But i have 22 columns in my sheet. How do i aggregate based on just on of the columns ?

90. Nick says:

When I try using your formulas (for distinct) I keep getting a ERROR:504 (using Open Office, and I have changed the commas to semi-colons). What am I missing? (Thank you in advance!)

91. Thomas says:

Hello,
I am trying to create a list of names taken from a column which contains duplicate names within it.
I want to create a separate table (so not effecting the data itself) which displays the most common values down to the most unique.
For example:
David
Peter
Paul
Albert
Jules
Hector
David
Albert
Hector
Hector
David
David

This would then display in a separate table/sheet as:
David
Hector
Albert
Paul
Peter
Jules

Kind Regards
Thomas

• Hi Thomas,

You can do it in this way:

1. In the original table (Sheet1), count the number of name occurrences using this formula.

2. In Sheet2 (or anywhere you want), extract the distinct names as described in this example.

3. In Sheet2, replace the formulas that extract names with values using Paste Special (it's necessary because you will need to sort the list later, and Excel has problems with sorting 2 formula-driven columns).

4. In Sheet2, enter a Vlookup formula to pull the occurrence numbers from the original table.

5. Sort the distinct list by the occurrences column.

I've created a small example for your reference and you can download it here. The source data is in Sheet1, the result on Sheet2.

92. Anu says:

Hey,

I did formula in one cell. After that if i drag down that formula will give incorrect detail. How to solve this? which keys i have to press?

93. JTB says:

Fantastic...nicely done...thank you!

94. Eric says:

Hi there,

First, this is a wonderful site and I appreciate all your effort. I wonder how you can use Excel to:

I have three columns all with 7 digit numbers. All three have overlapping values. I am trying to only find the values in column A that are not found in Column B or C.

Find Unique Values in Column A not found in Column B or Column C?

Any assistance would be greatly appreciated.

Eric

95. Funmi says:

Hi Ms. Svetlana, I need help. I need to be identify data that comes with the wrong code.Please look at the data below
Account Number AccountType Account Class
1012929311 01 INDSAV
1015619275 01 QUACOR
1015658317 01 QUACOR
1015680666 01 QUACOR
1040135492 01 WISACC
1040148603 02 WISACC
All account class-WISACC is meant to have an Account Type of 01, how do identify the ones that come with O2 easily

• Hello Funmi,

Is my understanding correct that a text string, say "1040135492 01 WISACC", is input in a single cell? If so, you can highlight wrong entries by creating a conditional formatting rule with this formula:

=AND(NOT(ISERR(FIND("dress",A2))),ISERR(FIND("01 dress",A2)))

Where A2 is the top-most cell with data.

If the data is in 3 different columns, then create a rule with this formula:
=AND(\$C2="WISACC", \$B2<>1)

Where B2 is Account Type and C2 is Account Class.

The step-by-step instructions to create a conditional formatting rule can be found here: How to create a conditional formatting rule with a formula.

• Desmond says:

HiThere

Can I extend until 500 - \$AF\$6:\$AF\$500. Its looks like when I replace to 500 the formula cannot works'

=IFERROR(INDEX(\$AF\$6:\$AF\$16, MATCH(0, COUNTIF(\$AG\$5:AG5,\$AF\$6:\$AF\$16) + (COUNTIF(\$AF\$6:\$AF\$16, \$AF\$6:\$AF\$16)1), 0)), "")

96. Sandepe says:

for unique value, you can use =IF(COUNTIF(F\$2:'F2,F2)=1,F2,"")its working as well

97. Ronell says:

Hi Ms. Svetlana;
Just need some help.

In column A and B as shown below:

Column A Column B

00123 Revised
00124 Revised
00123 Cancelled

*As you can see "00123" appeared twice but with different
value on Column B., any formula that can count the last
occurrence of the data in Column A with the value on
Column "B"

Thanks for the Help.

98. Colin says:

Hello,

I'm trying to get my sheet to track unique and duplicates on "sheet2", then note how many of each from "sheet1" on "sheet2" next to the unique and 1st duplicate.
the biggest issue I'm having is my "names" are all numerical values.

99. David Godinger says:

Could you please provide the distinct formulas for a list that's in a row? To my disappointment, I wasn't able to understand what you did well enough to adapt it. Thanks for the fine work!

• Hello David,

In fact, the formulas for extracting distinct values from a column and a row are very similar, and you have to adjust just one reference. Please have a look at the following examples.

Here's the basic distinct formula discussed in this tutorial:

=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 (you enter the distinct formula in B2 and then copy it down the column).

When extracting distinct values from a row, instead of referencing the cell above the first cell of your distinct list, you refer to the cell to the left of the distinct list. For example:

=IFERROR(INDEX(\$A\$1:\$F\$1, MATCH(0, INDEX(COUNTIF(\$A\$2:A2, \$A\$1:\$F\$1), 0, 0), 0)), "")

Where:

- \$A\$1:\$F\$1 is the source list.
- A2 is the cell to the left of the first cell where you enter the formula.

hi,
is there any way to get unique data from two column with formula (without helper cell or column or row).
for example, convert this data:
a b

to :
a b

• Mel says:

Hi,

My question is related to MOHAMMAD's. If Sheet 1 contains:

A B