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:
Recommended articles
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
How to create an array formula
- Double press with left mouse button 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.
Recommended articles
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.
Recommended articles
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.
Get excel *.xlsx file
Unique distinct list sorted alphabetically.xlsx
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Excel categories
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.
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/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/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.
Get the Excel 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)) Get the Excel *.xls file unique-list-sorted-alphabetically-with-a-condition.xls
Excellent, 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/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. Press with left mouse button 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)) Get the Excel *.xlsx file Create-a-unique-distinct-alphabetically-sorted-list-with-criteria-v2.xlsx
[...] https://www.get-digital-help.com/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 attached 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
Press with left mouse button on 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 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
Press with left mouse button on 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))