Create a unique distinct alphabetically sorted list, extracted from a column in excel
Table of contents
- Create a unique distinct alphabetically sorted list
- Create a unique distinct alphabetically sorted list with criteria
I came up with a new idea when I reread a previous article Sorting text cells using array formula in excel. Maybe it is possible to sort the new unique distinct list alphabetically.
To the right is a picture of the list I will be working with. I created named ranges, List (A2:A10) and a named range for the start cell of the list, List _start (A2).
Here is the formula in B2:B10. See picture below.
Edited 2009-06-28 Here is a shorter formula:
Array formula in cell B2, see picture below.
How to create an array formula
- Select cell B2
- Click in formula bar
- Copy and paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell B2
- Copy cell (not formula in formula bar) (Keyboard shortcut: Ctrl + c)
- Select cell range B3:B11
- Paste (Keyboard shortcut: Ctrl + v)
Remove errors (Excel 2007), array formula:
and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.
How to customize formula to your workbook
Change named ranges in formula.
Download excel sample file for this tutorial
unique-list-sorted-alphabetically.xls
(Excel 97-2003 Workbook *.xls)
Create a unique distinct alphabetically sorted list with criteria
Array formula in cell D2:
Array formula in cell E2:
Download excel *.xlsx file
Create a unique distinct alphabetically sorted list with criteria.xlsx
Functions in this article:
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
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
SMALL(array,k)
returns the k-th smallest row number in this data set.
ROW(reference)
returns the rownumber of a reference
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
Related posts:
Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
Filter a column and create a new unique list sorted from A to Z using array formula in excel
Extract a list of alphabetically sorted duplicates from a column in excel
Create a unique distinct sorted list containing both numbers text removing blanks in excel
Recommended categories
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: http://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: http://www.get-digital-help.com/2010/07/11/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/
[...] http://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
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.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 http://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
[...] http://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?