## Create a unique distinct sorted list containing both numbers text removing blanks

*Article updated on February 11, 2018*

**Table of contents**

- Create a unique distinct sorted list containing both numbers text removing blanks
- Create a unique distinct sorted list containing both numbers text removing blanks
**with a condition**

### Create a unique distinct sorted list containing both numbers text removing blanks

**Array formula in cell B2: **

**How to create an array formula**

- Copy above array formula
- Select cell B2
- 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.

**Named range**

List ($A$2:$A$15)

What is a named range?

**Download excel sample file for this tutorial. **

Unique and Sort-numbers-and-text-cells-using-excel-array-formula.xls

(Excel 97-2003 Workbook *.xls)

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

### Download excel *.xlsx file

unique-list-sorted-alphabetically-with-a-condition1.xlsx

**Functions in this article:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**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

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

### 26 Responses to “Create a unique distinct sorted list containing both numbers text removing blanks”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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