## 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 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.

### 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 sorted values based on corresponding values in another column

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

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 […]

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 […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

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. 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. Click in the name box (next to the formula bar) and name the range. In this case, list.

3. Press Enter