Create a unique distinct sorted list containing both numbers text removing blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A to Z and also ignoring blank cells.
Array formula in cell D3:
How to create an array formula
 Copy above array formula
 Select cell D3

Click in formula bar
 Paste formula (Ctrl + v)
 Press and hold Ctrl + Shift
 Press Enter
How to copy array formula
Copy cell B2 and paste it down as far as needed.
Explaining formula in cell D3
There are two formulas in the IFERROR function, when the first formula is running out of numbers to return the second formula starts extracting text values.
IFERROR( formula1, formula2)
Step 1  Prevent extracting duplicate numbers
The COUNTIF function counts cells in cell range based on a condition or criteria. If the value is equal to 0 then it has not been displayed yet. The first cell reference grows when the cell is copied to cells below, this makes the formula aware of previously displayed value above the current cell.
COUNTIF($D$2:D2,$B$3:$B$16)=0)
becomes
COUNTIF("Sorted list",{"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"})=0
becomes
{0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 2  Identify numbers in column
The ISNUMBER function returns TRUE if value is a number.
ISNUMBER($B$3:$B$16)
becomes
ISNUMBER({"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"})
and returns
{FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}
Step 3  Multiply arrays
Both arrays must return TRUE for the logical expression to return TRUE. In ordeer to achieve AND logic I multiply the arrays. TRUE * TRUE = TRUE, TRUE * FALSE = FALSE and FALSE * FALSE = FALSE.
(COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16)
becomes
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}
and returns
{0;1;1;0;0;0;0;0;1;0;0;1;0;0}. 0 (zeros is the equivalent to FALSE and any other number is equal to boolean TRUE.
Step 4  Replace TRUE with numbers in column
The IF function returns one value (argument2) if TRUE and another (argument3) if FALSE. The IF function returns "A" if logical expression is FALSE, this makes the SMALL function ignore the text value in the next step.
IF((COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16), $B$3:$B$16, "A")
becomes
IF({0;1;1;0;0;0;0;0;1;0;0;1;0;0}, $B$3:$B$16, "A")
becomes
IF({0;1;1;0;0;0;0;0;1;0;0;1;0;0}, {"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"}, "A")
and returns {"A";8;12;"A";"A";"A";"A";"A";9;"A";"A";9;"A";"A"}.
Step 5  Extract smallest number
To be able to return a new value in a cell each I use the SMALL function to filter numbers from smallest to largest.
SMALL(IF((COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16), $B$3:$B$16, "A"), 1)
becomes
SMALL({"A";8;12;"A";"A";"A";"A";"A";9;"A";"A";9;"A";"A"}, 1)
and returns 8 in cell D3.
Explaining formula in cell D6
Step 1  Prevent duplicates and only extract text values
This step and forward explains how to extract text values. We begin with cell D6 which is the first cell that extracts text values in our example. The ISTEXT function returns TRUE if value is a text value. The IF function returns a number representing the rank order if the list were sorted from A to Z.
IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), "")
becomes
IF({1;0;0;1;1;0;1;1;0;1;0;0;1;1}, COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), "")
becomes
IF({1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 0; 0; 1; 1}, {5; 0; 3; 0; 7; 0; 3; 4; 1; 2; 0; 1; 0; 6}, "")
and returns
{5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}.
Step 2  Extract kth smallest number
SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1)
becomes
SMALL({5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}, 1)
and returns 0 (zero).
Step 3  Find number in array
The MATCH function finds the relative position of a value in an array or cell range.
MATCH(SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1), IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0)
becomes
MATCH(0, IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0)
becomes
MATCH(0, {5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}, 0)
and returns 4.
Step 4  Extract value based on position
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($B$3:$B$16, MATCH(SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1), IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0))
becomes
INDEX($B$3:$B$16, 4)
and returns "AA" in cell D6.
Create a unique distinct sorted list containing both numbers text removing blanks with a condition
Array formula in cell F4:
How to create an array formula
How to copy array formula in cell F4
 Select cell F4
 Copy (Ctrl + c)
 Select cell range F5:F10
 Paste (Ctrl + v)
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I [โฆ]
Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 [โฆ]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column [โฆ]
Lookup and return multiple values sorted in a custom order
Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to [โฆ]
Sort column based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large [โฆ]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined [โฆ]
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I [โฆ]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event [โฆ]
Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A [โฆ]
26 Responses to โCreate a unique distinct sorted list containing both numbers text removing blanksโ
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
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.
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. 19 10100 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)), "")
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.
Is there a way to do this without it being a array formula?
john dalton,
Perhaps there is, try this:
https://www.getdigitalhelp.com/2013/02/04/nomorearrayformulas/
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,
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
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
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. Click in the name box (next to the formula bar) and name the range. In this case, list.
3. Press Enter