Filter unique distinct values, sorted and blanks removed from a range
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.
Answer:
The image above demonstrates an array formula in cell B8 that extracts numbers and text in sorted order, numbers first and then text from A to Z based on cell range. If you want to use a faster way than an array formula then check out Extract unique distinct sorted values from a cell range [UDF].
New shorter array formula in cell B8:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
You need to customize the formula so it suits your worksheet. It is easy, simply replace all instances of $B$7:B7 in the formula above with a cell reference to the cell right above the cell you enter the formula in in your worksheet.
Example, you are about to enter the formula in cell F7 in your worksheet, you now need to replace $B$7:B7Â with a reference to the cell right above cell F7 and that is F6, however, it must look like this:Â $F$6:F6. It is a growing cell reference that will expand automatically when you copy cell F7 and paste to cells below, I will explain it in greater detail below.
Named range
The formula above contains a named range tbl, it is simply a reference to a cell range and they are easily and quickly created.
- Select cell range B2:E5.
- Type tbl in Name Box.
Explaining new formula in cell B8
There are two parts in the new array formula above, the first part extracts numbers from the cell range and the second part extracts text values.
The IFERROR function moves from the first part of the formula to the second part as soon as an error is detected in the first part. An error is created in the first part when there are no more numbers to extract.
IFERROR( formula_part1, formula_part2)
Step 1 - Identify previous values
The COUNTIF function counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to the cells below. This makes the formula aware of values displayed in cells above. 0 (zero) indicates values that not yet have been displayed
COUNTIF($B$7:B7, tbl)=0
becomes
COUNTIF($B$7:B7, B2:E5)=0
becomes
{0,0,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, TRUE, TRUE}
Step 2 - Find not empty cells
The less than and larger than sign combined is interpreted as not equal to by Excel. B2:E5<>"" means
tbl<>""
becomes
B2:E5<>""
and returns
{TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}
This array contains boolean values indicating if cell is empty (FALSE) or not empty (TRUE).
Step 3 - Divide numbers with array
tbl/((COUNTIF($B$7:B7, tbl)=0)*(tbl<>""))
becomes
{"Banana", "Orange", "Pineapple", 8;8, 9, "Apple", "Peach";"Pear", 0, "Mango", "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"}/((COUNTIF($B$7:B7, tbl)=0)*(tbl<>""))
becomes
{"Banana", "Orange", "Pineapple", 8;8, 9, "Apple", "Peach";"Pear", 0, "Mango", "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"}/({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}*{TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE})
becomes
{"Banana", "Orange", "Pineapple", 8;8, 9, "Apple", "Peach";"Pear", 0, "Mango", "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"}/{1,1,1,1;1,1,1,1;1,0,1,1;1,1,1,1}
and returns
{#VALUE!, #VALUE!, #VALUE!, 8; 8, 9, #VALUE!, #VALUE!; #VALUE!, #DIV/0!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!}
Step 4 - Extract k-th smallest number in array
The AGGREGATE function lets you extract the k-th smallest number ignoring error values.
AGGREGATE(15, 6, tbl/((COUNTIF($B$7:B7, tbl)=0)*(tbl<>"")), 1)
becomes
AGGREGATE(15, 6, {#VALUE!, #VALUE!, #VALUE!, 8; 8, 9, #VALUE!, #VALUE!; #VALUE!, #DIV/0!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!}, 1)
and returns 8 in cell B8.
Step 1 - Explaining second part of formula in cell B10
The following steps explain how to extract text values sorted from A to Z ignoring blanks. This step extracts a number indicating the alphabetical sort order of each text value.
Note that it is the formula in cell B10 I am explaining now. The ISTEXT function returns TRUE if the cell contains a text value.
COUNTIF(tbl, "<"&tbl) returns an array containing numbers representing the alphabetical sort order of each text value.
COUNTIF($B$7:B7, tbl&"") makes sure that values displayed above current cell is ignored, we don't want to extract duplicate values.
IF(ISTEXT(tbl)*(COUNTIF($B$7:B7, tbl&"")=0), COUNTIF(tbl, "<"&tbl), "")
becomes
IF({TRUE, TRUE, TRUE, FALSE; FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}*(COUNTIF($B$7:B7, tbl&"")=0), COUNTIF(tbl, "<"&tbl), "")
becomes
IF({TRUE, TRUE, TRUE, FALSE; FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}*{TRUE, TRUE, TRUE, FALSE;FALSE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, COUNTIF(tbl, "<"&tbl), "")
becomes
IF({1,1,1,0;0,0,1,1;1,0,1,1;1,1,1,1}, {1, 7, 10, 0;0, 2, 0, 8;9, 0, 6, 3;11, 4, 5, 1}, "")
and returns
{1, 7, 10, "";"", "", 0, 8;9, "", 6, 3;11, 4, 5, 1}
Step 2 - Find smallest number and return a unique number based on row and column
The SMALL function extracts the smallest value in the array. The IF function compares the smallest number to the array in order to identify where the value is located.
COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl) returns !DIV/0 errors for all numbers in the cell range, we want to compare text values only.
(ROW(tbl)+(1/(COLUMN(tbl)+1)))*1 returns unique numbers for each value in cell range, we must avoid duplicate values in the array.
IF(SMALL(IF(ISTEXT(tbl)*(COUNTIF($B$7:B7, tbl&"")=0), COUNTIF(tbl, "<"&tbl), ""), 1)=IFERROR(COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(SMALL({1, 7, 10, "";"", "", 0, 8;9, "", 6, 3;11, 4, 5, 1}, 1)=IFERROR(COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0=IFERROR(COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0=IFERROR({1,7,10,0;0,2,0,8;9,0,6,3;11,4,5,1}/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0=IFERROR({1,7,10,0;0,2,0,8;9,0,6,3;11,4,5,1}/{TRUE, TRUE, TRUE, FALSE; FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}, ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0=IFERROR({1,7,10,0;0,2,0,8;9,0,6,3;11,4,5,1}/{TRUE, TRUE, TRUE, FALSE; FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}, ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0=IFERROR({1,7,10,#DIV/0!;#DIV/0!,#DIV/0!,0,8;9,#DIV/0!,6,3;11,4,5,1}, ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF(0={1,7,10,"";"","",0,8;9,"",6,3;11,4,5,1}, (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, "")
becomes
IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, "")
and returns
{"","","","";"","",3.2,"";"","","","";"","","",""}
Step 3 - Extract value based on unique number
IF(MIN(IF(SMALL(IF(ISTEXT(tbl)*(COUNTIF($B$7:B7, tbl&"")=0), COUNTIF(tbl, "<"&tbl), ""), 1)=IFERROR(COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, ""))=(ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, tbl, "")
becomes
IF(MIN({"","","","";"","",3.2,"";"","","","";"","","",""})=(ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, tbl, "")
becomes
IF(3.2=(ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, tbl, "")
becomes
IF(3.2={2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, tbl, "")
becomes
IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, tbl, "")
becomes
IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {"Banana", "Orange", "Pineapple", 8;8, 9, "Apple", "Peach";"Pear", 0, "Mango", "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"}, "")
and returns
{"","","","";"","","Apple","";"","","","";"","","",""}
Step 4 - Concatenate strings ignoring blanks
The TEXTJOIN function concatenates values ignoring blanks.
TEXTJOIN("", TRUE, IF(MIN(IF(SMALL(IF(ISTEXT(tbl)*(COUNTIF($B$7:B7, tbl&"")=0), COUNTIF(tbl, "<"&tbl), ""), 1)=IFERROR(COUNTIF(tbl, "<"&tbl)/ISTEXT(tbl), ""), (ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, ""))=(ROW(tbl)+(1/(COLUMN(tbl)+1)))*1, tbl, ""))
becomes
TEXTJOIN("", TRUE, {"","","","";"","","Apple","";"","","","";"","","",""})
and returns "Apple" in cell B8.
Old Excel 2007/2010 array formula in cell B8:
Get the Excel file
extract-a-unique-distinct-list-sorted-alphabetically-from-a-range-removing-blanksv2.xlsx
Excel 2007/2010 array formula: Filter duplicate values, sorted and blanks removed
Array formula in cell B8:
Get the Excel file
extract-a-duplicates-list-sorted-alphabetically-from-a-range-removing-blanks.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 […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Excel categories
13 Responses to “Filter unique distinct values, sorted and blanks removed from a range”
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.
Awesome. Unbelievable. This has got to be one of the coolest formulas ever. Well, certainly that I've had the need to dream requirements for. Thank you so much!
And you might also want to add to your bullet list at the top of the Answer section that it handles numbers and text together. Some of your other formulas weren't for both. I'd have to guess that you won't get many more requests for enhancing this because it does everything I can imagine and is just outright awesome. You've just made my day.
BTW, I'm not a big fan of named ranges for my application, so I hard code the range. The way I set it up is with headers in row A, the formula in A2 and drag it down. I have my original data range in B2:D4. Here's all of that hard coded. It's nothing more than a search and replace of yours, so there's no functionality change, just a formatting change to avoid named ranges and rearrangement of where things are.
=IFERROR(SMALL(IF(($B$2:$D$4"")*(ISNUMBER($B$2:$D$4))*(COUNTIF($A$1:A1, $B$2:$D$4)=0), $B$2:$D$4, ""), 1), IFERROR(INDEX($B$2:$D$4, SMALL(IF(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1)=IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), ROW($B$2:$D$4)-MIN(ROW($B$2:$D$4))+1), 1), MATCH(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1), INDEX(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), SMALL(IF(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1)=IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), ROW($B$2:$D$4)-MIN(ROW($B$2:$D$4))+1), 1), , 1), 0), 1), ""))
EEK,
You are welcome!
This is great. Do you have an example where the values are just on one column? Thanks!
JP,
I can´t find an example but I created a workbook. Check it out:
Unique-distinct-list-from-a-column-sorted-A-to-Z-blanks.xls
Hi guys,
i have question, what i must do if i want to have duplicates data.
i mean in sort list i want to see duplicates.
Can you help me plzzz
Goran,
read this post:
Sort a range from A to Z using array formula
Oscar,
Ty vm its helps :)
Oscar,
i read post but i have problem....what i must do or change in this formula to have duplicates data
=IFERROR(SMALL(IF((csh"")*(ISNUMBER(csh))*(COUNTIF($B$3:B19,csh)=0),csh,""),1),IFERROR(INDEX(csh,SMALL(IF(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1)=IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),ROW(csh)-MIN(ROW(csh))+1),1),MATCH(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1),INDEX(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),SMALL(IF(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1)=IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),ROW(csh)-MIN(ROW(csh))+1),1),,1),0),1),""))
Goran,
read this:
Filter duplicate values, sorted and blanks removed (array formula)
Is there a version of this that works if the "tbl" is actually 3 different columns (non-consecutive)? I used the post "Extract a unique distinct list from three columns in excel," (https://www.get-digital-help.com/extract-a-unique-distinct-list-from-three-columns-in-excel/) but that formula does not remove blanks, which I need. Also, sorting alphabetically is not necessary.
Thanks!
Ross,
I have added an array formula that removes blanks:
Extract a unique distinct list from three columns with possible blanks
Will this work with more columns and dynamic range (not named but using indirect)? I tried your new shorter formula in both Excel 2021 and Google Sheets, however, only a single value was listed. In Google Sheets, I placed the below formula in cell A3. Cell A1 contains the table's range which is $C$3:H. The table contains array formulas all on the 3rd row starting from col C. To be fair, this was also tested in Excel 2021, using the range on your first screenshot (B2:E5 or $B$2:$E$5) on A1, with static data instead of results of array formula, and pressing CTRL+SHIFT+Enter instead of using =ARRAYFORMULA(). What am I doing wrong and how do I adjust the formula to be able to accommodate a dynamic range if it doesn't handle it yet?
=ARRAYFORMULA(IFERROR(AGGREGATE(15, 6, INDIRECT(A1)/((COUNTIF($A$4:A4, INDIRECT(A1))=0)*(INDIRECT(A1)"")), 1), TEXTJOIN("", TRUE, IF(MIN(IF(SMALL(IF(ISTEXT(INDIRECT(A1))*(COUNTIF($A$4:A4, INDIRECT(A1)&"")=0), COUNTIF(INDIRECT(A1), "<"&INDIRECT(A1)), ""), 1)=IFERROR(COUNTIF(INDIRECT(A1), "<"&INDIRECT(A1))/ISTEXT(INDIRECT(A1)), ""), (ROW(INDIRECT(A1))+(1/(COLUMN(INDIRECT(A1))+1)))*1, ""))=(ROW(INDIRECT(A1))+(1/(COLUMN(INDIRECT(A1))+1)))*1, INDIRECT(A1), ""))))
Oscar, what worked for me is the formula from your newer post found in https://www.get-digital-help.com/extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-in-excel/ which is so much simpler than this solution but I tweaked it a bit to use "" instead of 0 for it to treat 0 as a value: =LET(x,SORT(UNIQUE(TOCOL(range))),FILTER(x,x""))