Create a unique distinct sorted list containing both numbers text removing blanks in excel
Array formula in cell B2:
Copy cell B2 and paste it down as far as needed.
Named range
List ($A$2:$A$15)
What is named ranges?
Download excel sample file for this tutorial.
Unique and Sort-numbers-and-text-cells-using-excel-array-formula.xls
(Excel 97-2003 Workbook *.xls)
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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
July 12th, 2010 at 1:37 am
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.
July 13th, 2010 at 8:51 am
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!
July 14th, 2010 at 2:02 am
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.
March 23rd, 2011 at 4:02 am
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.
March 23rd, 2011 at 10:09 am
Pat K
You can remove errors with IFERROR(value, value_if_error) function in excel 2007.
July 28th, 2011 at 1:04 am
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".
July 28th, 2011 at 1:10 am
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!
July 28th, 2011 at 9:01 am
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.
July 28th, 2011 at 12:57 pm
EEK,
read this post: Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed
November 1st, 2011 at 7:04 pm
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?
November 2nd, 2011 at 3:24 pm
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)
February 14th, 2012 at 9:58 pm
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.
February 14th, 2012 at 9:59 pm
Apologies, I did not complete the question. Basically is it possible to adapt the above to create the list in the correct order?
February 20th, 2012 at 2:31 pm
Harry,
Is this the problem?
I don´t know how to solve that, sorry.
February 20th, 2012 at 2:52 pm
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
March 13th, 2012 at 6:54 pm
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)), "")