## Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique distinct values are all values except duplicates.

Example, in column B value "DD" exists twice in cell B3 and B11. In column D value "DD" exists only once since it is a unique distinct list.

**Update 2020-12-09,** the formula below is for Excel 365 subscribers:

You can find an explanation here: Extract unique distinct values sorted from A to Z and other examples here: How to use the UNIQUE function

Use the array formula below if you own an earlier Excel version.

Array formula in cell D3:

### Watch a video that explains how to use it and how it works:

Learn how to filter values with a condition and return unique distinct values sorted from A to Z:

Unique distinct list sorted alphabetically based on a condition

Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users. =SORT(UNIQUE(FILTER(C3:C11, E3=B3:B11))) […]

Unique distinct list sorted alphabetically based on a condition

### How to create an array formula

- Double click on cell D3
- Copy (Ctrl +c) amd paste (Ctrl+v) above formula to cell D3
- Press and hold Ctrl + Shift simultaneously
- Press Enter once
- Release all keys

The formula in the formula bar should now look like this: {=*formula*}

Don't enter the curly brackets yourself, they appear automatically.

Array formulas allows you to do advanced calculations not possible with regular formulas.

### How to copy array formula

- Select cell D3
- Copy cell (Keyboard shortcut: Ctrl + c)
- Select cell range D4:D7
- Paste (Keyboard shortcut: Ctrl + v)

### Explaining array formula in cell

**Step 1 - Identify values not yet shown above current cell**

The COUNTIF function counts the number of times a value exists in a cell range.

Cell range $D$2:D2 changes as the formula is copied down to cells below. This makes it possible to avoid duplicate values in the list.

COUNTIF($D$2:D2,$B$3:$B$11)=0

becomes

COUNTIF("Unique list sorted alphabetically",{"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD"})=0

becomes

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

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

**Step 2 - Create an array with a ranking sort number**

COUNTIF($B$3:$B$11,"<"&$B$3:$B$11)

becomes

{1;3;5;3;7;0;5;7;1}

**Step 3 - Convert array with not displayed values to an array containing rank numbers**

The IF function converts not yet displayed values into alphabeically ranked numbers.

IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11,"<"&$B$3:$B$11),"")

becomes

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE},{1;3;5;3;7;0;5;7;1},"")

and returns

{1;3;5;3;7;0;5;7;1}

**Step 4 - Find the smallest value in array**

The SMALL function extracts the smallest number in the array.

SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11,"<"&$B$3:$B$11), ""),1)

becomes

SMALL({1;3;5;3;7;**0**;5;7;1},1)

and returns 0.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**Step 5 - Find relative position in the array**

The MATCH function finds the position of the next alphabetically sorted value.

MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11,"<"&$B$3:$B$11),""), 1),COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0,{1;3;5;3;7;**0**;5;7;1}, 0)

and returns 6.

**Step 6 - Return value in data based on row coordinate**

The INDEX function returns a value based on row and column number.

INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), ""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

becomes

INDEX($B$3:$B$11, 6)

becomes

INDEX({"DD"; "EE"; "FF"; "EE"; "GG"; "**BB**"; "FF"; "GG"; "DD"}, 6)

and returns BB in cell D3.

### Download excel *.xlsx file

Unique distinct list sorted alphabetically.xlsx

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Extract a unique distinct list and ignore blanks

Question: How do I extract a unique distinct list from a column containing blanks? Answer: Cell range B3:B12 contains several blank […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Extract unique distinct values A to Z from a range and ignore blanks

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

### 53 Responses to “Extract a unique distinct list sorted from A to Z”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Great formula. With your formulas, I always modify the formula to deal with blank spaces. My data usually contains blank rows. =INDEX(List,MATCH(MIN(IF(ISBLANK(List),"",IF(COUNTIF($B$1:B1,List)=0,1,MAX((COUNTIF(List,"<"&List)+1)*2))*(COUNTIF(List,"<"&List)+1))),IF(ISBLANK(List),"",COUNTIF(List,"<"&List)+1),0). At the isblank place, you can also add other conditions like a unique list where the letter for example begins with z.

To do a descending list, change "".

This does work with numbers. Is there a way to get this to work with numbers and text?

I meant to say it does not work with numbers.

I have tried to make this work with numbers, by using an if condition to coerce a range into text -(if(isnumber(list),text(list,"0"... but I found out that countif cannot coerce a range.

Sean,

See this post: https://www.get-digital-help.com/2009/06/06/sorting-numbers-and-text-cells-also-removing-blanks-using-array-formula-in-excel/

Oscar, Thanks. That is a great formula. Is there a way to make it so it can create a unique distinct list?

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

[...] https://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from... [...]

Thanks for the formula. How do I extend it beyond 10 lines?

If I copy the formula from B2 and Ctrl+Shift+Enter it into B11, B12 etc. I just see "BB" repeated.

--Thanks.

Jay,

Copy the cell, not the formula.

The formula contains relative cell references that change when you copy the cell.

Oscar.

I am using the formula for creating the list with criteria and I am running into a problem. My data is on a different sheet to where I am using the formula and I have referenced the cell 'name' and 'task'. I want to create a list of names based on the task they are doing. I have copied the formula and have entered it as an array and it has the { } brackets around it. All I am getting is one name repeated down the list. I'm not sure where I am going wrong.

Thanks, Ollie.

Ollie Wood,

I think you found the answer above. Copy the cell, not the formula.

Here it is again in greater detail:

1. Select the cell

2. Copy cell (Ctrl + c)

3. Select the cell range below.

4. Paste (Ctrl + v)

The formula contains relative cell references that change when you copy the cell.

Oscar

Thanks for the quick reply. I had read this and this is how I am coping it, using ctr c and v. It's still not right though. Can you offer any other pointers where I may be going wrong?

Thanks, Ollie.

Ollie Wood,

Send me your workbook (without sensitive data):

Contact form

Hi Ollie, I had the same problem, you have to remove any blank cells from the list, or use the modified version of the formula that deals with blanks.

Oscar is it possible to add something to the sample sheet so the formula ignores blank cells? I've tried adapting it myself but with no luck.

Rob,

Read this post:

Create a unique distinct sorted list containing both numbers text removing blanks

Hello, Oscar!

It is exciting material to me, I thought of something like that before but was not able to invent it.

But I cannot understand it. :(

For example - why are we making Max of CountIf result? As CountIf result should be a single value, than Max of it is going to be the same value isn't it?

Alexander,

why are we making Max of CountIf result? As CountIf result should be a single value, than Max of it is going to be the same value isn't it?No, The countif function returns an array of values.

Download the attached file and "Evaluate" the formula.

Wow interesting tool, thanks you! You opened me eyes!

The only issue "Evaluate" have is its unusable window - it doesn't format formula in any way, it dosn't allow copy, it doesn't allow resize.

In general it is just magic, thanks.

If you have time and will to answer -

Why

ROW(List)

works differently when I bring it out of the whole formula?

ROW(List) always return me row of the list start not the corresponding item's row.

Alexander

List is named range, example A1:A10.

Example 1,

Array formula: =ROW(List)

returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} but only 1 is visible in the cell.

Example 2,

Array formula: =IF(ROW(List)=1,1,0)

becomes

=IF({1; 2; 3; 4; 5; 6; 7; 8; 9; 10}=1,1,0)

becomes

=IF({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE},1,0)

and returns {1; 0; 0; 0; 0; 0; 0; 0; 0; 0}

but only the first value in the array is visible. A single cell can´t display all values in an array.

Thank you, very intresting!

Hope I don't mess here too much.

Wanted to ask - where did you learn excel that good from? May you suggest some good book or web resource.

As for me - I like Excel, use it where possible, but I'm not even close.

Hi Oscar,

Thank you so much for your very helpful posts - I look forward to receiving each one and find them very educational and useful.

I have a question about hyperlinks. I have a main table (which holds all my income and spend information) and I scan every receipt and put a hyperlink on the supplier of items I purchase which enables me to call up the receipt (which I save in pdf format).

I have built sheets using array formulas that query the main table based on dropdown boxes to identify record numbers meeting whatever criterion I want, and then display details of the records (using a simple offset function against the record number). However, to see any receipts, I have to go back to the main table and find the original entry to access the hyperlink.

Is there any way to display the hyperlink along with the other information I pull from the master table, so that I can access the receipts from the query sheet?

Thanks so much for your help.

Regards

Geoff Robb,

Save your receipt´s filepaths and file names in a column on your main table. I believe you can query and insert that data in your hyperlink formula on your query sheet.

Oscar,

Great work on the excellent posts. They are very helpful.

I am trying to use an adaptation of this idea, but am not able to get the formula working. In the first column I have a list that should be unique but may be unsorted. In the second column I have a Yes/No value to choose whether or not to include that row in the final result list. The final result list should be sorted. Is this possible?

For example:

List Include

AA Yes

CC Yes

BB No

DD Yes

EE No

Final result list:

AA

CC

DD

Thanks for your help!

Regards

Glen,

Array formula in cell G2:

=INDEX($D$2:$D$7, MATCH(MIN(IF((COUNTIF($G$1:G1, $D$2:$D$7)=0)*

($E$2:$E$7="Yes"), 1, MAX((COUNTIF($D$2:$D$7, "<"&$D$2:$D$7)+1)*2))*(COUNTIF($D$2:$D$7, "<"&$D$2:$D$7)+1)), COUNTIF($D$2:$D$7, "<"&$D$2:$D$7)+1, 0)) Download excel *.xls file unique-list-sorted-alphabetically-with-a-condition.xlsExcellent, that works like a charm. Well, almost... It turns out that the list could contain both text and numbers. If the list contains a number, this formula no longer works correctly.

I saw your separate post about sorting text and numbers https://www.get-digital-help.com/2009/06/06/sorting-numbers-and-text-cells-also-removing-blanks-using-array-formula-in-excel/ but it is not clear to me how to add the condition *($E$2:$E$7="Yes") to it.

As before, any guidance is greatly appreciated!

Glen,

read this post: Create a unique distinct sorted list containing both numbers text removing blanks with a condition

Brilliant, Oscar! That works perfectly.

Thanks for the help.

Why won't this work for a named range that is on another sheet? Sorry, newbie here.

Lauren,

If you are talking about this formula:

=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)) I does work but you have to enter it as an array formula. 1. Select a cell 2. Click on formula bar 3. Copy and Paste array formula in formula bar 4. Press and hold Ctrl + Shift 5. Press Enter

Hi Oscar, Great Formula!!

Just wondering, how can the original formula you used in the article be further expanded to account for a criteria. I'll explain further.

Using your example,

Column A Column B

List Weight

DD 1

EE 0

FF 0

EE 1

GG 0

BB 1

FF 1

GG 0

DD 1

DD 0

FF 1

AA 0

EE 0

How can I then create a unique list of Column A - say in column D - for those with 0 in column B, and then another unique (i.e. no repition of AA, BB) list of Column A - say in column E - for those with 1 in column B?

I've tried to manipulate the original formula you created, but failed. Would greatly appreciate your help. :)

Thanks :)

Peter

Peter,

read this:

Create a unique distinct alphabetically sorted list with criteria

Thanks. Perfect!!

Actually, I have a further idea, Say we had a third weight (say 2) and we now wanted a list of 1 and 0 together as one list, and 2 as a separate list. How would the formula be then? Because:

(...) *($B$2:$B$14=1)*($B$2:$B$14=0)* (...) Wouldn't work.

Thanks

Peter

Peter,

Array formula in cell D6:

=INDEX($A$2:$A$16, MATCH(MIN(IF((COUNTIF($D$5:D5, $A$2:$A$16)=0)*(COUNTIF($D$2:$D$3,$B$2:$B$16)), 1, MAX((COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)), IF(COUNTIF($D$2:$D$3,$B$2:$B$16), COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1,""), 0)) Array formula in cell E6: =INDEX($A$2:$A$16, MATCH(MIN(IF((COUNTIF($E$5:E5, $A$2:$A$16)=0)*(COUNTIF($E$2,$B$2:$B$16)), 1, MAX((COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)), IF(COUNTIF($E$2,$B$2:$B$16),COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1,""), 0)) Download excel *.xlsx file Create-a-unique-distinct-alphabetically-sorted-list-with-criteria-v2.xlsx

[...] https://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from... [...]

[...] Look here: Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital ... [...]

I was trying to use your technique, could I use an entire column instead of a Range? Range is fine actually I tried to rename it Destinations, but my real problem maybe I want the Range to be on Worksheet and the summary of unique values, they don't even need to be sorted on another Worksheet. I get a N/A when I try it. I try modifying the formual to start in Column A in cell 2 when making the list, the downloaded example works so I may peck away at that.

Suggestions?

Personally, I use the Google spreadsheets all the time for uniques, sorting, filtering and queries. It's so much better than Excel for that.

The simplest method in Excel is to

Highlight a range of data (make sure they all have a heading)

Go to Insert tab

Select Pivot Table

Choose new or existing worksheet, your choice

Click OK

In the PivotTable Field List find your column you want to sort

Drag that into the Row box at the bottom

You can now sort the list alphabetically if you want from the Row Labels Filter button.

You can go to Pivot Table options to remove headings, sorting options, auto width, subtotals and other stuff.

That's it.

VisicalcVeteran,

Personally, I use the Google spreadsheets all the time for uniques, sorting, filtering and queries. It's so much better than Excel for that.Competition is great!

The simplest method in Excel is toHighlight a range of data (make sure they all have a heading)

Go to Insert tab

Select Pivot Table

Choose new or existing worksheet, your choice

Click OK

In the PivotTable Field List find your column you want to sort

Drag that into the Row box at the bottom

You can now sort the list alphabetically if you want from the Row Labels Filter button.

You can go to Pivot Table options to remove headings, sorting options, auto width, subtotals and other stuff.

That's it.

A lot of steps if you have to repeat it many times.

An array formula and a named range is a lot quicker, in my opinion.

Yo!

Very good formula you have here, but very complicated and useful for few list only (hang on 500+ data). I have here my own and simple way, yet effective. I been using this for 3 years. Using only countif, vlookup and rank.

DATA is on Column C.

Formula to get unique values is on Column B

Formula to sort the unique values on Column A

The unique list in on column D.

A2=if($b2="zzz","",countif($b$2:$b$6,"<="&$b2))

B2=if(countif($c$1:$c2,$c2)=1,$c2,"zzz")

D2=iferror(vlookup(rows($d$2:$d2),$a:$c,2,0),"")

Row Column A Column B Column C Column D

1 Sort get Unique Data Unique list

2 2 mark mark ann

3 zzz mark mark

4 3 zack zack zack

5 1 ann ann

6 zzz zack

Hope this helps you as it did to me. I been using this to my 10,000 rows of name and get the sorted unique names.

mark

Awesome, thx! The issue with the solution proposed above - as you stated - is that it doesn't work for large tables (that's why the commenter above was seeing the same value in all rows, it wasn't a copy/paste issue, just slow calcuation). This is less elegant but more effective. Thanks!

[…] Here is a possible solution. I found this amazing formula for unique values online at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital … Note that the formula uses a few named ranges for simplicity, make sure you add those, If you want […]

Hi, It is amazing formual and it is working. However, despite trying hard, I fail to understand what the MATCH formula exactly doing in this. I am newbie for such complicated formulas.

So, Please explain in workds what following formula is doing.

[…] at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital … or filter -> advanced filter -> unique […]

[…] I can summarize my product data with a sumifs statement. I can get unique customer data from multiple from the method described here: unique distinct value […]

Hello,

In your 'Create a unique distinct alphabetically sorted list with criteria' which is fantastic by the way, I need to extend this to have a third column 'c' with its own criteria and a column 'f' which lists those indicated in 'c'.

To clarify:

column 'e' would be a sorted list from 'a' depending on 'b'

column 'f' would be a sorted list from 'a' depending on 'c'

Try as I might, I cannot seem to get the formula correct to do this, your help would be really appreciated.

Thank you

Tony

Great series of blogs Oscar. I think i almost have what I need. I'm struggling to get my list sorted alphabetically (it has a date filter and some key word filters in it already). I'm probably getting confused by all the syntax. Any chance of some tips?

Start Date B1

End Date B2

{=IFERROR(INDEX(TblDelivery, MATCH(0,IF(LEFT(TblDelivery[Product],3)="PRO",1,COUNTIF($B$5:$B5,TblDelivery[Product])+(TblDelivery[Date]>$B$2)+(TblDelivery[Date]<$B$1)),0),COLUMN(E1)),"")}

Thanks

Adrian

Adrian

This might work:

=INDEX(TblDelivery[Product], MATCH(MIN(IF((COUNTIF($B$5:$B5, TblDelivery[Product])=0)*(TblDelivery[Date]>$B$2)*(TblDelivery[Date]<$B$1), 1, MAX((COUNTIF(TblDelivery[Product], "<"&TblDelivery[Product])+1)*2))*(COUNTIF(TblDelivery[Product], "<"&TblDelivery[Product])+1)), IF((TblDelivery[Date]>$B$2)*(TblDelivery[Date]<$B$1),COUNTIF(TblDelivery[Product], "<"&TblDelivery[Product])+1,""), 0))