E-Mail 'Extract a unique distinct list sorted from A to Z' To A Friend
Email a copy of 'Extract a unique distinct list sorted from A to Z' to a friend
Loading ... Loading ...123 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.
This is exactly what I've been looking for... almost. It breaks if there are blank cells in the named range. Is there a way to get this to work if there are blanks in the range?
Thanks.
Dave,
see this blog post: https://www.get-digital-help.com/extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-in-excel/
i cannot use an array formula for certain reasons. is there any other way?
Can you use a vba macro? Maybe someone at https://www.excelforum.com/ can help you.
Thanks sooooo much man! U Have saved my life!!!!
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/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/
Thanks Oscar. This works great. For people, who want a descending sorted list, change the "" and change "+sum(" to "-sum(". This puts the numbers after the text. If you want a descending list of numbers with the numbers before the text, don't change the +sum to -sum.
Sean, isn't much easier just to use the ISTEXT function to produce the same result?
@ Oscar! Took me a whooping 3 hrs to work through the logic.... Not really gifted in this I guess! Thanks! Great formula too have for work related situations!
I was trying to use istext to convert the range into text values, but I have been told that is not possible with countif as it cannot coerce a range in memory. It would be so much easier if was able to do it that way.
[...] https://www.get-digital-help.com/create-a-unique-alphabetically-sorted-list-extracted-from... [...]
Assuming that this is more of a 'one time' type of requirement, it is also possible to achieve the desired result via:
1) Convert the numerical type of fields to actual numbers by:
a) copy any blank cell (which has a numerical value of zero) and
b) select the entire range of numbers & text cells and
c) choose
2) use the for the range ( ribbon in Excel 2007/2010)
3) sort the list
Gets the same result just without the '#N/A' whic I'm assuming is not wanted anyway.
Pat K
You can remove errors with IFERROR(value, value_if_error) function in excel 2007.
Great formula. Thanks! But it looks like it has a slight formatting mistake. Whereas you refer to the original data range as "List" almost everywhere in the formula, you have it 'hard coded' in one place. See "$A$2:$A$15" in the middle of the formula. Just replace that reference with "List".
It's amazing how many useful formulas you have here, and you're very close to what I would consider the 'ultimate' but I can't find it.
I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple columns) not just a list in one column.
I see where you have formulas which act on MxN but not with all the features of this one:
1. Unique and distinct
2. Remove blanks
3. Sort
4. Properly handle numbers and text
And just to ask for the 'frosting on top' remove errors.
Thanks!
EEK,
Great formula. Thanks! But it looks like it has a slight formatting mistake. Whereas you refer to the original data range as "List" almost everywhere in the formula, you have it 'hard coded' in one place. See "$A$2:$A$15" in the middle of the formula. Just replace that reference with "List".
Thanks, I have edited this post.
EEK,
read this post: Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed
Great piece of art.
I have a question here:
Assuming the range List is in B19:B39
Shortened list is in C80:C85
How can this formula be amended to show correct values?
ahmed,
Instructions:
1. Create a named range (named List) and use cell range B19:B39
2. Select cell C80 and type in formula bar:
=INDEX(List, MATCH(MIN(IF(ISBLANK(List)+COUNTIF(C79:$C$79, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1))), IF(ISBLANK(List)+COUNTIF(C79:$C$79, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)) 3. Press and hold Ctrl + Shift 4. Press Enter once 5. Release all keys. Copy formula 1. Select cell C80 2. Copy (Ctrl + c) 3. Select cell range C81:C85 4. Paste (Ctrl + v)
Hi Oscar,
Thanks for these amazing formulas. If the numbers in the list include single and double digit numbers and I need the list to be ordered correctly i.e. 1-9 10-100 and then text. Currently the formula produces 1,10,11,12etc and then 2,20,21 etc.
Apologies, I did not complete the question. Basically is it possible to adapt the above to create the list in the correct order?
Harry,
Is this the problem?
I don´t know how to solve that, sorry.
Hi Oscar,
The problem is not quite as you say but very similar. The list being used in the formula changes dynamically based on a choice made elsewhere on the spreadsheet. This master list is either text or a list of numbers which are read as text. I fixed my problem for now by adding a 0 before any number which is less than 10. This means I do not need to sort the data as it now comes into my sheet corted correctly.
For those people searching through this and have a very long master list that needs to be compacted into a much shorter unique list I found that your formula can take a while to calculate and slows the sheet down. I was keen to avoid macros whilst building the s/s but the following macro works quite well and is relatively easy to adapt.
Sub RF_list()
With Sheets("Master")
.Range(.Range("F2"), .Range("F65536").End(xlUp)).Copy
End With
Sheets("Tables").[H3].PasteSpecial Paste:=xlValues
ActiveSheet.Range("$H$3:$H$10000").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveWorkbook.Worksheets("Tables").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tables").Sort.SortFields.Add Key:=Range("H3"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Tables").Sort
.SetRange Range("H3:H899")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Hope this helps someone out there.
Regards
Harry
Sean, awesome stuff. In one of the first comments you state " For people, who want a descending sorted list, change the "" and change "+sum(" to "-sum(". This puts the numbers after the text. If you want a descending list of numbers with the numbers before the text, don't change the +sum to -sum."
I would like to have the numbers descending followed by the text. You note indicates I should change the "" and leave everything else. However, you don't indicate what I should change the "" to. Thank you for your help. I am using the following formula as base:
IFERROR(=INDEX(List, MATCH(MIN(IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1))), IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)), "")
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!
Can you help me?
I have a name products column and their prices (other column).
I want to create unique distinct list with products name sorted by SUM of prices. Is it real using array formula?
Bill,
read this post:
Filter unique distinct list sorted based on sum of adjacent values
Thanks, Oscar!
great, but keep getting a #NUM! error after cell b2
any reaason this would calculate REALLY slowly?
brendan,
Array formulas working with large data sets can be slow. It also depends on your computer hardware.
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
Hi Oscar,
I am trying to use the formula for the list in Column C and paste at column G. Changing that in the formula is not helping.
Could you please help.
Sorry, found exactly where I was going wrong.
[...] 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 am using the following formula. How can i get it to work if there are blank cells?
How can i get it to work if there are formulas in the column?
=IFERROR(INDEX(List1,MATCH(MIN(IF(COUNTIF($F$9:F9,List1)=0,1,MAX((COUNTIF(List1,"<"&List1)+1)*2))*(COUNTIF(List1,"<"&List1)+1)),COUNTIF(List1,"<"&List1)+1,0)),"")
Jimmie,
try this formula:
=INDEX(List, MATCH(MIN(IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1))), IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)) Get the Excel file Unique-and-Sort-numbers-and-text-cells-using-excel-array-formula-works-with-formulas.xls
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?
Hi
I'm getting a problem when i using your formula in two different sheet. Although its working in the single sheet.
On same sheet reference.
+ CTRL + SHIFT + ENTER
I'm using this on different sheet reference.
+ CTRL + SHIFT + ENTER
Plz correct my formula.
Abhinav,
Try this formula on the other sheet:
That worked like a charm... :)
Thanks so much Oscar...!!!
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.
Hi Oscar, have been reading your blog over the last few weeks and have learnt a bunch of new techniques as a result.
I have been working with one of the unique list array formulas and it works a treat. I was wondering however if it is possible to add a criteria outside the array to effectively filter the list by a further level? The formula I have is
=IFERROR(INDEX(STATSUBCAT, MATCH(MIN(IF(ISBLANK(STATSUBCAT)+COUNTIF(D7:$D$7, STATSUBCAT), "", IF(ISNUMBER(STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT)+SUM(IF(ISNUMBER(STATSUBCAT), 1, 0))+1))), IF(ISBLANK(STATSUBCAT)+COUNTIF(D7:$D$7, STATSUBCAT), "", IF(ISNUMBER(STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT)+SUM(IF(ISNUMBER(STATSUBCAT), 1, 0))+1)), 0)),"")
I'm working (as a learning process!) on a personal budget spreadsheet. Using bank statements and the adding a column to categorize the transactions then SUMIF in the adjacent column I have a helpful look into my total catergory spending.
At the moment I am getting what the array formula is supposed to do; provide a list of all unique category types. But say I wanted to see what categories appeared between 2 dates? I tried adding a simple IF statement into the array formula just to select one date to see if I could get any matches to return. It did not so I guess I'm inserting the statement at the wrong position assuming this is possible at all?
Thanks again for a taking the time to put together such an instructive site.
To save anyone looking into this I should say I had my problem on its head. I had all I needed all along and simply needed to add a SUMIFS formula to filter the original data.
Just confirms stepping away from a problem for an hour or so is often the best thing you can do.
Duncan,
Yes, you can use sumifs. Have you tried a pivot tables?
https://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
https://www.contextures.com/CreatePivotTable.html
Hi Oscar,
I'm thinking pivot tables will have to be on my to learn list but for now it's info overload and I'm still getting to grips with some of the other stuff first. Also I have an awful lot of catergories in my Unique list that certainly benefit in terms of screen real estate from just displaying the ones needed to display the results.
To which end can could you help me abbreviate the formula so that it is only concerned with text entries and blanks (I'm only using the formula to create the unique category and subcategory expense description). I've noticed as this workbook is developing the CPU usage is starting to get out of hand on this moderate i3 3.2ghz machine. I'd rather not have to resort to manual calculation if I can help it.
I've stripped all the volatile date filtering out, TODAY() etc and that doesn't appear to be the source of the high CPU call?
I keep breaking the formula when I have tried to strip out the number functions, I hopefully as I get more familiar with the syntax I'll be able to say I have a better degree of understanding of how all the parts sit together.
Best,
Doh! Sorry Oscar were you suggesting I keep the Array for the Unique value then use those as row labels for the different column headers?
The jpg attached is an early draft and so far I've only SUMIFS the date ranges for the main expense catergory, the next block is the subcatergory to the right will hopefully receive the same treatment CPU overhead allowing.
Can I employ a Pivot table when the Catergory descriptions may change. I suppose in time all catergories will appear but that said I am really liking the idea of this unique array deal.
If you think its possible I'll be happy to go off and do the proper researh
Thanks again
Duncan,
Pivot tables lets you create unique values and sum corresponding values. It is really easy.
You can also filter the data using dates.
Oscar,
How right you were. What a revelation!
Took me a while to spot the Group.. function to assist in gathering my Date column headers into months but once I did I now have everything I need and at hardly any CPU cost.
I am now looking at the multiple consolidation ranges to see if this will allow me to get the necessary summary overview of all account pivot tables.
Thanks again..
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.
Is there a way to do this without it being a array formula?
john dalton,
Perhaps there is, try this:
https://www.get-digital-help.com/no-more-array-formulas/
[…] at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital … or filter -> advanced filter -> unique […]
Hello,
I've tried the V2 formula, and it works well. But, apparently it'll make a blank result too if i make a blank list in the column A. I wanna skip the blank list without breaking the formula logic, can anyone resolve this? Thanx.
Oscar,
I need to sort from largest value to smallest and continue to have issues. I'm working with values and have flipped SMALL to LARGE and Min to MAX. I must be missing something simple, could you please point me in the correct direction?.
Appreciate all the help.
Alex
wow man, u blew me away.. i've been looking for this for a long time. ur algorithm is bright. Ive tried PIVOT TABLE but it wont update if u copy it, ADVANCED FILTER but u have to do it over and over. im using a multiple table report and this what im trying to create but no luck. u did great bro.. ive search about circular references coz i know that would do it but its too complex for my level.. thanks bro. ur the man!!!
[…] 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 […]
How about "Z to A"?
Thanks lots!
Great formula! You guys are amazing and I appreciate what you do. I'm having one particular issue with the formula.
I want to reference the unique values created by this formula, in order to calculate other values in my workbook. The issue I'm having is that every time excel calculates, the unique list changes.
For example, here is one unique list:
A1B
A1B*UP
A1F
A1F*UP
When I press F9 to calculate, I get the following (probably due to the sorting)
A1B*UP
A1B
A1F*UP
A1F
Is there any way to have it so that the unique values sort consistently one way or the other?
Thanks!
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
Hello,
Congratulations. The formula is amazing.
The "Create a unique distinct sorted list containing both numbers text removing blanks with a condition" example is almost perfect for me.
But I´d like to remove the DISTINCT condition.
Could you please help me?
Regards
Whenever I use this I keep getting a blank in the second row for the created list.
Other than that it is working fine. I just don't understand what would cause that.
Thanks! It worked for me. I used this to remove blank spaces from a total of 2520 entries.
If it is an option, I have gotten in the habit of using this formula:
=IFERROR(INDEX(A:A,MATCH(0,INDEX(COUNTIF($B$1:B3,A:A),0,0),0)),)
And simply starting in B4 with B1=BLANK B2=0 and B3="Header"
That way it reads blanks and 0s as non-unique values in the list, and they are ignored. I will then hide rows 1:2.
The formula looks for values in the range (A:A in the example) not previously occurring in the list above the current cell ($B1:$B3 in the example). B4 examines $B$1:$B3, B5 examines $B$1:$B4 and so on.
Perhaps not the most elegant way, but it gets the job done and does not require arrays or more complex formulae.
Nice Formula/Solution!
Hi, the formula only works if I paste it into cell B2. If I try to change the formula cell reference from B1 to the cell that I need (cell C33), it returns an #N/A error. Do you have any advice? Thanks.
Great Formula!
What if I want to create a unique distinct sorted list containing both numbers and text, removing blanks with 2 or more conditions?
Thanks,
Brett
Is there a way to apply this to a filtered range and have it ignore hidden values? I'm using this formula and it works really well, but it shows every unique value in my range (R12:R1200) including the hidden ones. Any ideas?
=INDEX($R$12:$R$1200, MATCH(0, IF(ISBLANK($R$12:$R$1200), 1, COUNTIF(Z2:$Z$2, $R$12:$R$1200)), 0))
Hi Rod,
Yes, there is.
https://www.get-digital-help.com/extract-unique-distinct-values-from-a-filtered-table-udf-and-array-formula/
Thank you, Oscar! That worked! I'll rely to that post with a related question.
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))
Hi,
I'm just applying the LEFT() function on 'list' as
left(list,2), I'm getting error as
"There's a problem with this formula.
you type: =1+1, cell shows: 2"
Any help regarding this?
Qadeer Ahmed
You need to create a named range.
1. Select the cell range you want to use.
2. Press with left mouse button in the name box (next to the formula bar) and name the range. In this case, list.
3. Press Enter
Thank You!
This formula doesn't work for at all - I either get an entire list of n/a, or a value that doesn't even exist on my list!
I have a column of dates: I just want another column to show me a list of all unique dates in the first column.
Joy
You need to adjust the cell references in the formula so they point to your data:
=LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)
Cell reference $D$2:D2 is different, if you enter the formula in cell G5 the cell reference becomes $G$4:G4. meaning it should always be a cell reference to the adjacent cell right above.
You then copy the cell and paste it to cells below as far as needed, this will automatically change the relative cell references in the formula.
Hey! So Excel has a "UNIQUE" function now. Do you please have any ideas on how to extract them from that function?
Andrew,
yes, use the FILTER function to exclude blank cells.
=UNIQUE(FILTER(B3:B12,B3:B12<>""))
Hello Oscar,
Great and very helpful website!
I am looking to return a unique list across multiple columns and rows. I had assumed I could just expand
$B$3:$B$12to my criteria$A$5:$G$30but this just seems to return #N/AIs there a way of obtaining a unique list based from multiple columns and rows?
What I am ultimately looking to do is list them in the order of most frequent result at the top
Ollie,
Is there a way of obtaining a unique list based from multiple columns and rows with most frequent result at the top?
Yes, there is. Read this:
Extract a unique distinct list across multiple columns and rows sorted based on frequency
Have been using the UDF to great effect but I have been asked if there is a way to add a list of other items to exclude along with the blanks
I know I can modify the line -
If Len(Value) > 0 Then ucoll.Add Value, CStr(Value) to include
If Len(Value) > 0 And Value "Cancelled" And Value "N/A" Then ucoll.Add Value, CStr(Value)
but this is rather inelegant there must be a way to add an array or list to the top of the UDF which would be easier to manage but my VBA is a little to rusty to figure it out
Hi Oscar,
This doesn't work for a range across multiple columns. Do you know if it's a small adjustment to the formula to allow this or will it only work for a single column?
Kristo,
those formulas above work only for a single column, the following Excel 365 formula works with a multi-column cell range:
This formula works with many multicolumn cell ranges