Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition.
Table of Contents
- Extract a unique distinct list sorted from A to Z and ignore blanks
- Create a unique distinct sorted list containing both numbers and text removing blanks with a condition
- List unique distinct sorted values removing blanks based on a condition - Excel 365
- Create a unique distinct sorted list containing both numbers and text removing blanks with a condition - Excel 365
1. Extract a unique distinct list sorted from A to Z ignore 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 ignores blank cells.
Array formula in cell D3:
How to create an array formula
- Copy above array formula
- Select cell D3
- Press with left mouse button on 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 k-th 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.
2. 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)
3. List unique distinct sorted values removing blanks based on a condition - Excel 365
Update 10th of December 2020: Excel 365 subscribers can now use this much shorter regular formula in cell D3.
Here is how it works:
Extract unique distinct values sorted from A to Z ignoring blanks
4. Create a unique distinct sorted list containing both numbers text removing blanks with a condition - Excel 365
Excel 365 formula in cell F4:
Explaining formula in cell F4
Step 1 - Compare values in column Condition to value in cell F2
The equal sign is a logical operator that lets you compare value to value, it returns a boolean value TRUE or FALSE.
Table13[Condition]=F2
becomes
{"Yes"; "Yes"; "No"; "Yes"; "No"; "Yes"; "No"; "Yes"; "No"; "Yes"}="Yes"
and returns
{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}.
Step 2 - Check if values in column "Values" are not equal to nothing
The less than and greater than signs combined let you check if a value is not equal to another value, the result is a boolean value TRUE or FALSE.
Table13[Values]<>""
becomes
{"AA"; "CC"; "BB"; "DD"; "CC"; 0; 1; 2; 1; 2}<>""
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 3 - Multiply arrays - AND logic
The asterisk lets you multiply numbers or boolean values in an Excel formula. Multiplying boolean values lets you perform AND logic between two values.
TRUE * TRUE = TRUE
TRUE * FALSE = FALSE
FALSE * TRUE = FALSE
FALSE * FALSE = FALSE
AND logic means that all values must be TRUE in order to return TRUE.
Also, multiplying boolean values convert them automatically to their numerical equivalents.
TRUE - 1
FALSE - 0 (zero)
(Table13[Condition]=F2)*(Table13[Values]<>"")
becomes
{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1; 1; 0; 1; 0; 0; 0; 1; 0; 1}.
Step 4 - Filter values in Table13[Values]
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>""))
becomes
FILTER(Table13[Values],{1; 1; 0; 1; 0; 0; 0; 1; 0; 1})
becomes
FILTER({"AA"; "CC"; "BB"; "DD"; "CC"; 0; 1; 2; 1; 2},{1; 1; 0; 1; 0; 0; 0; 1; 0; 1})
and returns
{"AA"; "CC"; "DD"; 2; 2}.
Step 5 - Extract unique distinct values
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>"")))
becomes
UNIQUE({"AA"; "CC"; "DD"; 2; 2})
and returns
{"AA"; "CC"; "DD"; 2}.
Step 6 - Sort values from A to Z
The SORT function sorts values from a cell range or array
Function syntax: SORT(array,[sort_index],[sort_order],[by_col])
SORT(UNIQUE(FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>""))))
becomes
SORT({"AA"; "CC"; "DD"; 2})
and returns
{2; "AA"; "CC"; "DD"}.
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
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 […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
Excel categories
26 Responses to “Extract a unique distinct list sorted from A to Z ignore 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.
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. 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)), "")
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.get-digital-help.com/2013/02/04/no-more-array-formulas/
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. 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