## How to extract a unique distinct list from a column in excel

**Table of Contents**

- How to extract unique distinct values from a column
- Extract unique distinct values (case sensitive)
- How to extract unique values from a column
- How to extract unique distinct values from a column using advanced filter
- User Defined function: Extract unique distinct sorted values from a cell range (vba)
- Filter unique distinct values and records from multiple sheets add-in

**Overview**

Unique distinct values are all cell values but duplicate values are removed.

*Thanks to Eero, who contributed the original array formula!*

**Example sheet - How to remove duplicate values
**

Column A contains names, some cells have duplicate values. An array formula in column B extracts an unique distinct list from column A.

**Array formula in cell B2:**

*Thanks, Eero!*

or use this regular formula:

**Formula in cell B2:**

**How to create an array formula**

- Copy above array formula.
- Double click cell B2.
- Paste (Ctrl + v).
- Press and hold Ctrl + Shift.
- Press Enter.
- Release all keys.

*and copy cell B2 down as far as necessary.*

**Named ranges**

In excel you can name a cell range, a constant or a formula. You can then use the named range in a formula, making it easier for you to read and understand formulas.

*Example*

List : A2:A20

Tip! Use dynamic named ranges to automatically adjust cell ranges when new values are added or removed.

**How to create a named range**

- Select cell range A2:A20
- Type
*List*in name box - Press Enter

**Array formula and named range in cell B2:**

**Excel 2007 users can remove errors using iferror() function:**

*and copy it down as far as necessary.*

The formula is an array formula. To create an array formula you press Ctrl + Shift + Enter after you have entered the formula.

**Excel 2003 users can remove errors using isna() function:**

*and copy it down as far as needed.*

**How to handle blank cells in a range**

Harlan Grove created a formula to count unique distinct values from a list with blanks. I used the same technique to filter unique distinct values:

Array formula in cell B2:

### How the array formula in cell B2 works

**Step 1 - Create an array with the same size as the list**

=INDEX(List,MATCH(0,**COUNTIF($B$1:B1,List)**,0))

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given condition

COUNTIF($B$1:B1,List) returns an array containing either 1 or 0 based on if $B$1:B1 is found somewhere in the array *List *.

COUNTIF($B$1:B1,List)

becomes

COUNTIF("Unique distinct list",{Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas} )

and returns:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

This means the cell value in $B$1:B1 can´t be found in any of the cells in the named range List. If it had been found, somewhere in the array the number 1 would exist.

**Step 2 - Return the position of an item that matches 0 (zero)**

MATCH(lookup_value;lookup_array; [match_type] returns the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF($B$1:B1,List),0)

becomes

MATCH(0,{**0**;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

and returns 1.

**Step 3 - Return a cell value**

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

*=INDEX(List,1) *

becomes

=INDEX({**Federer,Roger**; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas}, 1)

*and returns "Federer, Roger"*

**Relative and absolute cell references**

When you copy the array formula down the countif formula range (*$B$1:B1)* expands. This is created by using relative and absolute references.

The first cell, B2: *COUNTIF($B$1: B1,List)*

Second cell, B3: *COUNTIF($B$1: B2,List)*

and so on.

Interested in learning powerful excel array formula and functions?

Check out the Advanced excel course.

### Functions in this article:

**MATCH(**lookup_value;lookup_array; [match_type])

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)**

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**ROW(**reference**)** returns the rownumber of a reference

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**LARGE(**array,k**)** returns the k-th largest row number in this data set.

### Download excel sample file for this tutorial.

Extract-a-unique-distinct-list-in-excelv4.xlsx

(Excel 2007 Workbook *.xlsx)

### Recommended blog posts

Want to learn more about **filtering unique distinct values**? You must read these blog posts:

- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Unique values from multiple columns using array formulas
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria

Learn more about **sorting** unique distinct values. Read these blog posts:

- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Create a unique list and sort by occurrances from large to small

Learn more about **counting unique distinct values and records**. Read these blog posts:

Thanks a lot!!! You've made my day!

Hey there - thanks a lot for this tip - though I initially could not get this to work - eventually I figured out MY problem the INDEX rownum was using "ROW(A2:A20)-1", when it should be using "ROW()-1" - otherwise it ALWAYS returns the start of the array.

Thank you for visiting Get Digital Help and commenting! I have changed the formula and the attached file. Using named ranges simplifies formula customization a lot!

Many thanks for the useful information posted on this website! However, as I am working on a very large set of data (from between 50 - 8000 records) and whenever I change the name range to go beyond the 1000th record, the unique list value would return error. I have tried to name the whole column A but it also doesnt work.

would be grateful if you could assist on this.

Many thanks for all your effort!

Thank you!

I tried to recreate your error, but failed. I have attached an excel file with over a 1000 records that works for me. The excel file is at the end of the above blog post. Try it and see if the excel file returns an error.

I am using excel 2007, what version are you using?

Have you changed cell ranges for both named ranges in your excel sheet?

List (A2:A20)

unique_start (B2)

Maybe you have a blank cell somewhere in your list? A blank cell returns an error. See this post:

http://www.get-digital-help.com/2009/03/10/how-to-automatically-create-a-unique-list-and-remove-blanks/

There is a somewhat shorter approach:

Let initial items in colA be named as "List". In B2 enter the

following array-formula:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) and copy it down as far as necessary, to get unique list.

That is really amazing, you made the array formula even shorter!

To remove any errors, Excel 2007 users can use IFFERROR() function:

=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"") + CTRL + SHIFT + ENTER and copy it down as far as necessary.

Thank you for your valueable comment!

hi.

thanks for the formulas (eero's and the revised one) guys. it really cured my headaches.

for the unique list generated (Column B), can the...

1) unique list sorted A-Z order?

2) unique list sorted based on occurrence (highest to lowest)?

the trick is: can the sorting be done without complicating eero's formula above?

reason i'm asking because the other posts on sorting is too complicated for my head to wrap around ;)

thx!

Interesting questions!

I´ll see if I can come up with some good solutions.

Thanks for commenting!

thanks!

in case you're wondering, eero's formula does not need the unique list range (Column B) to be same size as the main list (Column A).

I just need to copy down the formula to get the rest of the unique list.

Your other posts on sorting is based on range-based array formula (i.e. Column B must be same size as Column A in order for the array formula to work). This works well if you have short list, but if u have thousands of entries, unique list range must equal the same.

One other that thing that i noticed is the ranged-based array formula is fixed (you can't delete those 'empty' cells in this range).

anyways, thanks again for looking into another solutions and not to forget your valuable blogs here!

David, here is the answer to your first question.

unique list sorted A-Z order?Array formula in cell B2, see picture above.

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)) + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.

If anyone can come up with a shorter formula, i would be happy!

oh wow! it works! :)

THANKS!

David, here is the answer to your second question.

unique list sorted based on occurrence (highest to lowest)?Array formula in cell B2, see picture above.

=INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1), 0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary, to get sorted based on occurrence (highest to lowest)

I have to think some more to get this formula shorter, I don´t like the length of it.

works great! :D

thanks!

p/s: as long as the array formula works, i'm happy. but do keep up your great job on the formulas! MS Excel gurus should visit and give you a thumbs up! :)

Wow the short formula works, amazing!!!

Thanks!

Wow! This is just amazing. Works great. I would love if someone could explain how this works step by step. I tried, but couldn't. I am trying to extend this fomula with a user defined formula.

[...] How to extract a unique distinct list from a column in excel [...]

Sriram Venkitachalam,

I now have tried to explain the array formula, see the new section above.

Thanks for commenting!

Would you please advise me on how to created named range which includes A-Z sorted list of UNIQUE values of other list?

Thanks a lot for this example. But is there also a way without using CSE? because a use a tool where i import Excel file which does not support CSE formulas in Excel.

Hopefuly someone has an idea

Dear All, It's my first post right here, and I'd like to thank you all with special THANKS for ADMIN. Well, I'm looking for more info and explanation about the current topic of http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ It has mentioned above,

"How this array formula works: =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) The array formula uses MATCH() to find the first 0 (zero) value in the COUNTIF array. When you copy the array formula down the COUNTIF formula range expands. The first cell: COUNTIF($B$1:B1,List), the second cell: COUNTIF($B$1:B2,List) and so on"

It's understood, but I'm still puzzled for the following reasons:

1. When applying the mentioned formula, the result is always the first record only.

2. All array elements shows INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) in formula bar, which indicates that the range expansion mentioned above is not applicable.

3. The attached example sheet shows

@ cell B2: INDEX(List,MATCH(0,COUNTIF(B1:$B$1,List),0)),

while @ cell B3: INDEX(List,MATCH(0,COUNTIF(B$1:$B2,List),0)),

and It’s OK, if we are talking about singular cell formula.

So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?

Do I miss something?

Is there any Excel settings should be takes place?

Please help & advise.

Thank YOU & BEST Regards,

Raymond,

You need to convert your formulas to values.

Copy and "Paste Special.." the values created by the CSE formula.

Select "Values" and click OK.

OnlineAlone,

Copy =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) + CTRL + SHIFT + ENTER into your first cell. (Cell B2)

Copy your first cell to the remaining cells.

The formula then automatically changes to:

Cell B3: =INDEX(List,MATCH(0,COUNTIF($B$1:B2,List),0))

Cell B4: =INDEX(List,MATCH(0,COUNTIF($B$1:B3,List),0))

and so on.

Oscar,

Thank you for your quick response.

OK, I got it.

OnlineAlone,

I missed this question:

So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?

You have found an error in the attached file.

I have now changed the formula in the attached file and uploaded it to this blog post again.

Here is an excellent explanation of absolute and relative references:

http://www.cpearson.com/excel/relative.aspx

hi all,

thanks for the great formula/array formula. it works great.

lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).

e.g.

1-Jan-2010 ProductA

1-Jan-2010 ProductB

1-Jan-2010 ProductC

1-Jan-2010 ProductD

2-Jan-2010 ProductA

2-Jan-2010 ProductB

2-Jan-2010 ProductC

...

...

the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.

In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.

the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?

thx!

David,

have you tried "Advanced Filter"?

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#advfilter

Thanks for commenting!

2 problems i see using Advanced Filter:

a) The first 2 generated entry is a repeat. i.e. 1-Jan-2010, 1-Jan-2010, 2-Jan-2010, 3-Jan-2010, and so on.

first entry is named “Extract”).

b) The dates from my master table is set with a dynamic named range. Advanced Filter will generated unique

distinct list, manually.

I daily update the table

thx!

david,

See this blog post (and the attached file):

http://www.get-digital-help.com/2010/02/10/create-a-unique-distinct-list-of-a-long-list-without-sacrificing-performance-using-vba-in-excel/

just noticed this standalone post. incredible.

will try it out.

note: reason why Advance Filter is not preferred (by me anyway) is the list will gets longer daily. i wouldn't want to keep (manually) using Advanced Filter.

'sides, i'm preparing the file for Excel noobs. he/she should not be complicated by the steps :D

the VBA macro button is interesting tho! will try it now!!

thx guys!!

I been trying to do this for a long time. I tried by pivotTable to extract the list without duplicate items.

This much easier, clear, and great! Thanks a lot!

I found out the column 'A' must choose the title of it 'A1' also, to avoid in column 'B' shows 'A2' twice, and there is no problem to choose 'A1' coz it shows the same title in 'B1'.

Thanks again!

In excel 2003 and older you can remove error by using ISNA() function.

The formula is brilliant but it took me some significant amount of time to understand it (use Evaluate formula).

Wiciu,

Thanks!! I have added the ISNA() function to this post.

/Oscar

Oscar, these countif function formulas are great because they are able to extract a list with blank cells in the range. Is there a way to extract a list using sum(if instead of countif because this will work with closed workbooks (when the need arises), while also having the ability like the countif function to handle blank cells in the range? Maybe we need to put a condition in the front of the formula to handle the blank cells. Keep up the brilliant work. This website is one of the top Excel websites out there.

Sean,

Thanks!

As far as I know, you can´t extract a unique distinct list from a closed workbook with excel formulae.

Sean,

See this post: http://www.get-digital-help.com/2010/05/10/extract-unique-distinct-numbers-from-closed-workbook-in-excel-formula/

Sean,

Here is another post: http://www.get-digital-help.com/2010/05/12/extracting-unique-distinct-text-values-from-a-closed-workbook-in-excel-formula/

Oscar,

Thanks for the great article! Is it possible to modify the array formula to return the entire reduced list in a single cell? Instead of taking up as much real estate as the original list. For example the function '=offset(A1,0,0,10)' would return a list 10 numbers long in one cell. Not too useful in that cell, but I can then refer to only that cell for a data validation list or use it to do additional processing to.

Trey,

Data Validation was designed to work with lists of cells. I don´t know how to solve your problem.

I suppose another way to word it would be, I'm looking to return a list. The result of the above formula is a single value. Can array functions return a list instead of a single value? I am guessing, but isn't the result of typing "=Offset(A1:A10,0,0)" into B1 a list in cell B1? If your formula could output a list into one cell (say B2 from the example), a Data Validation reference could be made to just B2 instead of B2:B20.

As far as I know, "=Offset(A1:A10,0,0)" + CTRL + SHIFT + ENTER creates an array. You would only see the first value in cell B1. Creating a data validation reference to cell B1 won´t work. Only the first value is used.

Can array functions return a list instead of a single value?

Yes, but not in a single cell.

THANK YOU THIS HELPED. IT TOOK ME A WHILE TO FIGURE OUT... WHAT REALLY HELPED WAS BEING ABLE TO DOWNLOAD THE EXCEL FILE SO THANKS!

I tried to use the =IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))) + CTRL + SHIFT + ENTER and copy it down as far as needed.

Its not working. I get a blank cell only. Could you give some guidance. Working with excel 2003.

Thanks,

Greg

I forgot to mention I do have blank values and that the list is on a different sheet than what the formula would be does this cause a problem?

Thanks again,

Greg

Greg,

Yes, I think that causes a problem.

See this post: http://www.get-digital-help.com/2010/07/11/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/

Thanks for your comments!

Greg,

This is how I handled blanks in a range with this formula.

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))

Oscar,

I use this formula a lot. By adding conditions in front of the countif, I can extract a list with numbers, etc.

Is there a way to extract a list from Column A based on the total for this value being above say 30 in column B? I have been trying to use Sum(if to make it work without any success. The result below would be CC.

aa 10

aa 15

cc 40

cc 5

Sean,

That is the formula I should have written as an answer. Great contribution! I have added the formula to this post.

THANKS!

Extract a unique distinct list from column A where the total for this value in column B are above 30:

Array formula in cell C2:

=INDEX(list, MATCH(0, IF(SUMIF($A$1:$A$4, $A$1:$A$4, $B$1:$B$4)>30, COUNTIF($C$1:C1, list), ""), 0)) + CTRL + SHIFT + ENTER.

Fantastic Oscar. I was told that sumif cannot coerce a range, which could make the next part more difficult. I am looking for the values that are not equal to zero correct to 2 decimal points. Sometimes the sum is 0.0005, which is zero correct to 2 decimal points.

Is there a way to do this?

Oscar, the sum could be 0.000789 or -0.0000046464. These are rounding differences. Using Sum(if instead of sumif could easily coerce the entire range in memory to 2 decimal points.

Oscar, I figured it out.

INDEX(List, MATCH(0, IF(ROUND(SUMIF($A$3:$A$10, $A$3:$A$10, $B$3:$B$10), 2)0, COUNTIF($E$1:E1, List), ""), 0)).

Dear Oscar,

I try to find out if it is possible to rank the outcome of the most forecoming uniques in the 'Unique distinct list'. Like a top 10 (or 25) list of the most found uniques?

I work here with a huge list, it can be large as +12.000 rows. From this list i want to find out the top 10/25 most forecoming uniques, and ranked them. Most found uniques on top of list.

Sokolum,

First create the unique distinct list.

Use advanced filter to create a unique distinct list. Your list is huge so the formula on this page is too slow.

Read more about advanced filter.

Next to the unique distinct column create a formula to calculate occurences. Somtehing like this: =COUNTIF($A$1:$A$12000, C2) + ENTER. Copy the cell and paste it down to the end of your unique distinct list.

And last, sort the unique distinct list and occurences largest to smallest.

Thanks sean and oscar!

I used: =INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))

All I get is the NA# error now. All the blanks are at the end of this list name

Thanks,

Greg

I think I mentioned this before but my data is on different sheets and I am using excel 2003.

Thanks,

Greg

These results is exactly i was looking for. Indeed that a formula is slow. Now i will try to find out if i can automate this by a script.

Greg Savinda,

Did you adjust the relative reference? Bolded in formula:

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF(

$B$1:B1,list)),0))Oscar,

I have used the following:

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF(Sheet1!$D$1:D1,list)),0))

Would this error be to the fact that it is in a different sheet?

Thanks,

Greg

Greg Savinda,

I don´t think so.

Did you adjust the named range to your list?

Did you forget CTRL + SHIFT + ENTER?

Is it possible to extend this by matching items that meet a criteria?

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A

Frank Branch A

Frank Branch A

Joe Branch A

Mary Branch B

Jane Branch C

Mike Branch A

Joe Branch A

Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

Anura,

See this post:

http://www.get-digital-help.com/2010/09/01/extract-a-unique-distinct-list-by-matching-items-that-meet-a-criterion-in-excel/

I can't believe the speed of the response! I will test out with some real data from one of my reports and see what happens. Thanks for this.

[...] gospieler says: September 8, 2010 at 10:34 pm This tutorial show you How to extract a unique distinct list from a column in excel (without using advanced filter) http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-... [...]

Oscar,

I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates are in row A1:F1 and I would like the ordered, unique distinct list to be in G1:L1, and also display a blank once the data runs out. How do I alter your formula to accomplish this? Thanks!

Aamer,

Read this post: Excel udf: Remove duplicates from a large dataset

and this post: Remove duplicates and sort dates by each row in excel

The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on the last 4 characters in a cell. This would mean using the right function.

1845-2145

1846-2145

1845-2176

The unique list here is 2145 and 2176. Is there a short but sweet formula like the original countif formula above?

Sean,

Values in cell A1:A3:

1845-2145

1846-2145

1845-2176

Array formula in cell B2:

=RIGHT(INDEX($A$1:$A$3, MATCH(0, COUNTIF($B$1:B1, RIGHT($A$1:$A$3, 4)), 0)), 4) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far as needed.

Oscar!

THANK YOU SOOOOOOOOOOOOOOOOOO MUCH!!!! You have no idea how long I've been looking for this!!! I was told it couldn't be done but here it is! You've done it and without using any macros!!!

Many, Many Thanks!!!!

Keep up the AWESOME work!

Charlie,

I am happy you found what you were looking for! Blog reader Eero is the creator of this formula.

Oscar, it works great. I also tried it with the mid function. I thought countif does not work with arrays. The following formula will not work with countif- =TEXT(A1:A3,mmmmyy).

Sean,

Read this post: Remove duplicate text strings based on the 4 last characters in a cell in excel

Is there a way to create a unique distinct list using as criteria the values that do not exist in another column?

COLA ColB

A Z

B A

C B

D C

D X

The result is Z and X.

The formula I have used to create criteria based on another column

=INDEX($B$1:$B$4, MATCH(0, IF($A$1:$A$4="A", COUNTIF($D$4:$D4, $B$1:$B$4)), 0))

Sean,

Array formula in cell C2:

=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.

Oscar,

It works fantastic. Thanks for all your help.

[...] You might notice in Debra's workbooks that the DinnerPlan worksheet has dinner items listed multiple times. In order to create a unique list for the recipe lookup dropdown list, I used the code found at Extracting a Unique List. [...]

[...] A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula [...]

Actually, there is shorter way without having to do the "array-formula."

The regular formula is: =IF(COUNTIF($A$1:A1,A1)=1,A1,"")

As soon as the formula finds the first one, it will blank-out the dups.

*****Note: Must sort the list first

[...] Copy cell A2 and paste down as far as needed. See this blog post for an explanation: How to extract a unique distinct list from a column [...]

The formula is even faster if you use countifs in Excel 2007 instead of countif. Countifs calculated 7,000 rows with 3 criteria very quickly for me.

Sean,

Great tip, I didn´t know that!

This has been very helpfull, thank you!, going to spend alot of my free time on this site, thanks! :)

=INDEX(List,MATCH(0,if(list2=A1,COUNTIF($B$1:B1,List)),0))

A1="a". Now I want to change the condition to list2=A2 ("b"). Is there a way to move onto the next condition ("b") automatically after it finds all the unique entries for "a"?

A1="A"

A2="B"

Sean,

If order is important, try:

=INDEX(List,IFERROR(MATCH(0,if(list2=$A$1,COUNTIF($B$1:B1,List)),0), MATCH(0,if(list2=$A$2,COUNTIF($B$1:B1,List)),0))

Not important, try:

=INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))

Oscar,

It works great. This saves time in pulling a big list. Countif is so flexible or countifs if using Excel 2007. The only thing now is that I need another column showing if it is an A or B for the unique entries in List.

Dog A

Cat A

Cow B.

Oscar,

I think I figured it out. Just change the index to list2.

=INDEX(list2,MATCH(0,IF(COUNTIF($F$3:$F$4,list2),COUNTIF($F$8:F8,list)),0))

Keep up the great work.

Oscar,

If there is two duplicates with one equal to "A" and the other equal to "B", it only picks up the duplicate once. It should be twice since they have different conditions.

I would like to use =INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))

dog a

cat a

dog b

Result

dog A

cat B

How do you fix this?

Sean,

I think you will find the answer in this article: Vlookup with 2 or more lookup criteria and return multiple matches in excel

The original extraction of names from a list works, but on trying the sort order formula, it doesn't. It DOES pick up the 1st alphabetized name, but then just repeats. And yes, the cell ref does increment:

=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)...

=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A2, List1)...

The full formula is: =INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)=0, 1, MAX((COUNTIF(List1, "<"&List1)+1)*2))*(COUNTIF(List1, "<"&List1)+1)), COUNTIF(List1, "<"&List1)+1, 0))

I tried sorting the original formula list, and it worked for a moment, and then flipped back. Wild! Formulas in excel I just don't get. I'm an admin, not a coder :) Any help please?

Larry,

I think your formula is copied from here: Unique distinct list from a column sorted A to Z using array formula in excel

How to create an array formula1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

2. Press and hold Ctrl + Shift.

3. Press Enter once.

4. Release all keys.

I have edited the original post, I hope I have explained it in greater detail than before. Read: Unique distinct list from a column sorted A to Z using array formula in excel

The formula doesn't work, is says that a circular reference is created, I downloaded the Excel file with the example and it gives NA as a result.

Dave,

I downloaded the file and it worked here.

Im having the same problem as dave, ive downloaded the sample file and it looks fine when you view it, but as soon as you click into the Formula and press enter it removes the "{" around the statement and it then returns NA. Im using Excel 2007.

Ah! pressing the control + shift after pasting into the formula bar sorts it, what does that actually do ?

That´s how you enter an array formula.

How to create an array formulaCopy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

Press and hold Ctrl + Shift.

Press Enter once.

Release all keys.

I don't know why I can't make this work ...

To be sure I'm not doing anything wrong I made an example like yours but the array formula gives me an error for the first "List" and appears "#NAME?"

I downloaded your example and there is working just fine.

Now I pasted my data in your example and it calculates only for the first 20 rows like in the example. So it isn't working for the whole column ..

Do I need to create a list or something?

pau,

you are right. I forgot to add how to create a named range.

I have now added how to create a named range in this blog post.

Hi Oscar,

This is fantastic.

I tried using this formula and it really helped.

I have a small problem that I am not sure on how to solve.

I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a pivot table, I want to get the count as 3 since that is the unique count. But I am getting 15 as the value!

I am assuming that the pivot table is reading the formula as a value and counting it. Any way to circumvent that?

ExcelBeginner,

read this post: Count unique distinct values in a pivot table in excel

From your article - http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

You mentioned a formula to make lists from one column in alphabetical order. I've used this formula with success for a couple years but just stumbled on a problem I hadn't encountered before. For some reason it isn't functioning in the attached worksheet with the range I am searching in. You'll see in the attachment that in the Stats sheet D3 I have entered the formula

=IFERROR(INDEX(DlvDT1,MATCH(MIN(IF(COUNTIF($D$2:$D2,DlvDT1)=0,1,MAX((COUNTIF(DlvDT1,"<"&DlvDT1)+1)*2))*(COUNTIF(DlvDT1,"<"&DlvDT1)+1)),IF(ISBLANK(DlvDT1),"",COUNTIF(DlvDT1,"<"&DlvDT1))+1,0)),"")

And it produces a "". The range being looked up is in the Deadheads spreadsheet (Deadheads!$I$2:$I$3000) and named DlvDT1.

I couldn't figure out what was causing it to mess up after a while so I started messing with the range values to see if that had something to do with it. Surprisingly it did. If I do the same formula using range name DlvDTOne (Deadheads!$I$2:$I$706) the results will show, if I make it anything after I706 it will not show.

I also tried to go from Deadheads!$I$639:$I$3000 (DlvDTUno and got it to work successfully but it will not go any lower than I639.

That tells me something in the values between I638 and I707 is causing issues with your formula format. Can you look into it and see what you can figure out? I've made all three tests I explained available in cells D3, D4, & D5 respectively. Let me know if you can figure anything out.

Thanks!

(P.S. - Tried to submit via your upload page and it wouldn't let me. Email me and I can send you the xlsx file.

Hi,

I have a table, I want to find which records that have criteria (example: column TOTAL > 0). PIVOT Table did not solve that I want.

I want relist this table into another sheet.

Could you help me? Thanks.

I was only able to get this to work in Excel2007 by switching to compatibility mode. What is different between the two versions that causes it to fail in Excel2007? Is there a version that works for Excel2007?

Hello,

I'm using VBS with Array

=FilterUniqueSort($A$2:$A$8212)

if there are no data I got #VALUE! how get rid of this please?

Sheet1,

VBA code:

This is actually code that I'm using from this link that is posted up

http://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls

here I got #VALUE! error

p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....

I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show data

Function FilterUniqueSort(rng As Range)

Dim ucoll As New Collection, Value As Variant, temp() As Variant

Dim iRows As Single, i As Single

ReDim temp(0)

On Error Resume Next

For Each Value In rng

If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)

Next Value

On Error GoTo 0

For Each Value In ucoll

temp(UBound(temp)) = Value

ReDim Preserve temp(UBound(temp) + 1)

Next Value

ReDim Preserve temp(UBound(temp) - 1)

iRows = Range(Application.Caller.Address).Rows.Count

SelectionSort temp

For i = UBound(temp) To iRows

ReDim Preserve temp(UBound(temp) + 1)

temp(UBound(temp)) = ""

Next i

FilterUniqueSort = Application.Transpose(temp)

End Function

Function SelectionSort(TempArray As Variant)

Dim MaxVal As Variant

Dim MaxIndex As Integer

Dim i, j As Integer

' Step through the elements in the array starting with the

' last element in the array.

For i = UBound(TempArray) To 0 Step -1

' Set MaxVal to the element in the array and save the

' index of this element as MaxIndex.

MaxVal = TempArray(i)

MaxIndex = i

' Loop through the remaining elements to see if any is

' larger than MaxVal. If it is then set this element

' to be the new MaxVal.

For j = 0 To i

If TempArray(j) > MaxVal Then

MaxVal = TempArray(j)

MaxIndex = j

End If

Next j

' If the index of the largest element is not i, then

' exchange this element with element i.

If MaxIndex < i Then

TempArray(MaxIndex) = TempArray(i)

TempArray(i) = MaxVal

End If

Next i

End Function

thank you for reply

Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one example

Col A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

etc...

also prevous question can this VBS insert or shift cells when new unique data appear?

Thanks

Sheet1,

This is actually code that I'm using from this link that is posted uphttp://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls

here I got #VALUE! error

I downloaded the excel file and it works here.p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....Yes it does, if the value is in the cell reference.I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show dataThe custom function can only process a single cell reference. I am not sure I understand..Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one exampleCol A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

etc...

Have you created an array formula with the custom function?Ok I just followed instructions listed abowe and copied VBS so I selected 100 cells and applied array on it..

array is pointed on column on another sheet, but if there are empty cells I got

Col A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

for all of them

How to stop to not shows to the end all 100 same error?

and is there any chance for this VBS instead of Error GoTo 0 put that is empty (doesn't show anythin)? as other formulas aroound when get 0 from col B start to showing #N/A

Thank you

Sheet1,

What is shown in the formula bar? What cell reference are you using?

Did you also copy the selectionsort function from the attached file?

Hi

Many Thanks for your site

I have used "the Extract unique distinct sorted values from a cell range (vba)" code and allmost everything is fine

But the first cell value in the sorted list is allways the first cell value from the input data, not correctly sorted

List1

B

C

D

A

FilterUniqueSort(list1)

B

A

C

D

Is there some way to fix it?

Hej Mats!

I recreated your setup but I can´t see anything wrong?

Hi

This is amazing

I deleted the VBA module and reimported

Now it works :-)

I have had very good use of your excelent code and information about array formulas

Many thanks

Mats

Oscar,

I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection Change AutoFilter on multiple colums. Is there a way to make the list only return the unique values that are visible in the filtered source data?

I see a similar answer above using just an array formula, but my source is too long for that to be practical. Any help would be greatly appreciated.

Robert Jr

Robert JR,

Great question, a blog post is soon coming up.

Robert J,

Read this post: Extract unique distinct values from a filtered table (udf and array formula)

When I do this formula in Excel 2007, it just produces the same list as the original. ??

Terry,

Did you remember to create an array formula? (Ctrl + Shift + Enter)

Did you adjust cell references (bolded)

$B$1:B1,List),0))Hi Oscar, thanks for your reply. Yes I did all that. What I discovered was, this formula only works when the two columns are physically adjacent to each other. I am unable to do this in this case, so my unique list is in another part of the spreadsheet. Seems like it should work though, since I am using a named reference for the source data. I suppose I could just reference the source data in a column next to the unique list as a workaround.

Terry,

They don´t have to be adjacent. They don´t have to be on the same sheet.

Download excel file:Terry.xls

Oscar, I copied my list into your sheet and it behaves the same way as it does in mine. I am using a list of nos. rather than text. Would this matter?

Terry,

I changed the text values to numbers and the formula filters unique distinct numbers.

See attached file:Terry1.xls

I am using the blanks formula below to recover a list when a set of numbers in a second identically sized list(List2) are blank

I was wondering if there is a way to modify this section of the formula so that it searches for a particular colum (Range "B:D") and takes rows 2 to 10 as the list. The column headings will be dates.

=INDEX($A$2:$A$10,MATCH(0,IF(ISBLANK($B$2:$B$10),"",COUNTIF($E$2:E2,$A$2:$A$110)),0))

Does anyone have any insight on this matter?

Cheers

Is there a way to take the results of an Array Formula (i.e. a list of names called CompScenList (a name range)) and gather the unique distinct values? I looked but could not see this question in the history...

Hello Oscar,

It's incredible that you've maintained this post for nearly three years, now I too have a question for you.

I am filtering my list from one sheet to another (Sheet 1 to Sheet 2), and everything seems to be in working order, except that my unique list is printing all my results twice:

Ashley

Ashley

Jenna

Jenna

Angie

Angie

Sarah

Sarah

Here is a copy of the formula I am using:

=INDEX(ClientList,MATCH(0,COUNTIF($A$1:A1,ClientList),0)) + Ctrl + Shift + Enter

What can I do to solve this?

Next, I am already using the first row on Sheet 1, and the first two rows on Sheet 2 to for column titles. When I attempt to change "COUNTIF($A$1:A1..." to "COUNTIF($A$3:A3..." I get a circular reference error. What should I do to make sure my unique list isn't checking for column titles?

Thank you in advance for your time!

-Philip

Please help to create a formula to pick all cells refering to a pertical condition.

Let me brief, suppose Colume A is having a list of employees (ofcourse, unique values) and Column B is having thier respective higher qualification (consider some employees with same qualification), now I want to put formula which enables to find list of all employees if I type a qualification (side by side) i.e if I type "MS" in Cell C1, the list of all employees, who are with "MS" in column B - should be there in D1, E1, F1........

Raja,

You can do exactly that with the formula in this post:

How to return multiple values using vlookup

Thanks for commenting!

Philip Adams,

If you enter the array formula in cell B3, the relative cell reference (bolded) in the formula must be:

$B$2:B2,ClientList), 0))When you copy the formula down to cell B4 the relative cell reference changes automatically to:

$B$2:B3,ClientList), 0))If you use the same cell reference as the current cell, you will get a circular reference warning.

Oscar,

Thank you very much for your speedy reply. I was wondering if it would be possible to add more criteria to the unique list. For example, if I only wanted the names in ClientList to appear if the text "Auto-Renewal" appeared in their respective column O.

Thank you again for your time.

-Philip

Philip Adams,

This post is probably interesting: Create unique distinct list from column where an adjacent column meets criteria

Hey Paul,

Trying to do something very easy, I'm sure. :)

I have a list that shows people who have been interviewed and whether they accepted or not. For the ones who have not, I want to have another table that lists their names and then I can comment on reasons for not accepting.

So I want a table like this:

Person1 - accepted

Person2 - not accepted

Person3 - accepted

Person4 - not accepted

To output to a table like this:

Person2

Person4

... and so on, in order...

Thanks!!

Q:

Considering A1: name

B1: Status

A2 to A10 person1, person2...

B2 to B10 accepted, not accepted...

in D1 accepted

in E1 not accepted

then in d2:

=IFERROR(INDEX($A$2:$A$10, SMALL(IF("accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(A1))), "")+Ctrl+Shift+Enter

and in e2:

=IFERROR(INDEX($A$2:$A$10, SMALL(IF("not accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(B1))), "")+Ctrl+Shift+Enter

With this you'll have the list of the accepted people in column D and the list of not accepted in column E.

file available at:

http://uploading.com/files/e76mam54/Q.xlsx/

Cheers,

Cyril.

You're awesome Cyril! It totally worked... thank you so much!

Thank you so much for the unique values formula. It saved tons of coding to replicate and it does it automatically.

Dear Mr. Oscar,

Excellent, thanks for spending your valuable time for me.

Really its amazing, i can't believe that alla these things can happend in excel, after reading these posts.

But in the answer you provided for me, some formulae are used, which were new to me. Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.

Thanks in advance

Chip,

Thanks, but the work is done by Eero.

Raja,

Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.I am sorry, I don´t know of any books about array formula basics. Maybe someone else can help me out here?

Cyril,

thanks for helping me out!

Oscar,

Why thank you!

your website is a gold mine, not only it is user friendly but it is filled with examples and real life cases, frankly i think you have here the best excel oriented forum and learning site.

Glad I could be of any assistance, but as I said, you are still the reference here.

kudos!

Raja,

Excel 2007 Bible by John Walkenbach

Publication Date: January 3, 2007 | ISBN-10: 0470044039 | ISBN-13: 978-0470044032

Excel 2010 Bible by John Walkenbach

Publication Date: May 10, 2010 | ISBN-10: 0470474874 | ISBN-13: 978-0470474877 | Edition: 1

Excel 2007 Formulas by John Walkenbach

Publication Date: February 12, 2007 | ISBN-10: 0470044020 | ISBN-13: 978-0470044025

Excel 2010 Formulas by John Walkenbach

Publication Date: May 24, 2010 | ISBN-10: 0470475366 | ISBN-13: 978-0470475362 | Edition: 1

Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques by Mike Smart

Publication Date: September 11, 2009 | ISBN-10: 0955459931 | ISBN-13: 978-0955459931 | Edition: 1

Microsoft® Office Excel® 2007: Data Analysis and Business Modeling by Wayne L. Winston

Publication Date: May 16, 2007 | ISBN-10: 0735623961 | ISBN-13: 978-0735623965

Microsoft Excel 2010 In Depth by Bill Jelen

Publication Date: July 5, 2010 | ISBN-10: 0789743086 | ISBN-13: 978-0789743084 | Edition: 1

Cheers

Congratulations for your job, Oscar.

How I can modify the formula to get to make disappear B2(DAVID) because the A1 and D2 have the same value

1/ A B D

2/ 2 DAVID 2

3/ 5 PETER 3

The formula that I use right now is this one:

[=SI.ERROR(INDICE(Devos!$A$2:$A$9999;COINCIDIR(0;CONTAR.SI($B$1:B1;Devos!$A$2:$A$9999);0));"")]

Thanks in advance

(Sorry,this way is understand better)

Congratulations for your job, Oscar.

How I can modify the formula to get to make disappear B2(DAVID) because the A2 and D2 have the same value

1/---A-----B-----D

2/---2---DAVID---2

3/---5---PETER---3

The formula that I use right now is this one:

[=IFERROR(INDEX($A$2:$A$9999;MATCH(0;COUNTIF($B$1:B1;$A$2:$A$9999);0));"")]

Thanks in advance

Jan,

See attached file:

Jan.xlsx

Firstly, great resource, fantastic work Oscar.

Quick question I have three lists of names in the same sheet and I need to pull them into three different distinct lists. I assume All i need to do is reference the correct fields E.G.

=INDEX(Primary,MATCH(0,IF(ISBLANK(Primary),"",COUNTIF($Q$1:Q1,Primary)),0))

=INDEX(Secondary,MATCH(0,IF(ISBLANK(Secondary),"",COUNTIF($Q$12:Q12,Secondary)),0))

The Primary list returns ok, the Seconday list retuns 0.

I can work with the Primary list for now as I'm trying to lookup a beginging date and end date based on the distinc list but am I doning something wrong with my secondary list?

Nick,

$Q$12:Q12, Secondary)), 0))You have to enter this array formula in cell Q13 for this to work. You also have to copy the cell Q13 (not the formula) and paste it down as far as needed. Make sure your named range

Secondaryis correct.Hi; Thank you for this article. Trying to better my Excel skill, and this topic is one I have been trying to get down for awhile.

I've downloaded your file, and at the very early stage, I am getting #N/A:

=COUNTIF($B$1:B1,List) returns a value of "0" as it is supposed to. But when I proceed to the next step, which is

=MATCH(0,COUNTIF($B1:B1,List),0)returns #N/A and I cant figure out why. Maybe it is a rookie error?

Thank you for you help!

Versch,

Array formula:

=MATCH(0,COUNTIF($B1:B1,List),0)

1. Select cell B2

2. Paste formula

3. Press and hold Ctrl + Shift

4. Press Enter

Hi Oscar,

Thank you for the update. You are correct, I had not made B2 into an array formula, which I now did as per your instruction. However, my

=MATCH(0,COUNTIF($B1:B1,List),0)

returns #N/A again.

Thank you; I will try re-engineering this till it works right.

I had a two coloumn, one coloumn have fifteen records and another have 100 records. both coloumn does't have the same records. i want to merge the both coloumn means i want to add that fifteen records against of against of 100 records (100*15) but i am not able to do that. For e.x. Column A-15 Unique Records and Column B has 100 Unique records and both are not matchning. I want to merge the both records (100*15) and make a single records in every row without missing any single records. Please suggest how to do that.

Aman Kr Tadneja,

Perhaps you are looking for this post:

Extract a unique distinct list from two columns using excel 2007 array formula

Hello Oscar,

Just wanted to leave a thank you note for the countless number of times your formulae have helped me get unique lists faster. I have been through all the "unique-list" posts on your site and have found them to be very useful. Thanks and keep up the good work!!

Playercharlie,

thanks for commenting!

The issue with #N/A is if you're using a named list that concludes a whole column. I had the same issue and could not figure out what iw as doing wrong as I kept getting #N/A or zero no matter what I tried.