18 Responses to “Create a unique distinct sorted list containing both numbers text removing blanks in excel”

1. Sean says:

   July 12, 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.

   Reply
2. chrisham says:

   July 13, 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!

   Reply
3. Sean says:

   July 14, 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.

   Reply
4. Pat K says:

   March 23, 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.

   Reply
5. Oscar says:

   March 23, 2011 at 10:09 am

   Pat K

   You can remove errors with IFERROR(value, value_if_error) function in excel 2007.

   Reply
6. EEK says:

   July 28, 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".

   Reply
7. EEK says:

   July 28, 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!

   Reply
8. Oscar says:

   July 28, 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.

   Reply
9. Oscar says:

   July 28, 2011 at 12:57 pm

   EEK,

   read this post: Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed

   Reply
10. ahmed says:

    November 1, 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?

    Reply
11. Oscar says:

    November 2, 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)

    Reply
12. Harry says:

    February 14, 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.

    Reply
13. Harry says:

    February 14, 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?

    Reply
14. Oscar says:

    February 20, 2012 at 2:31 pm

    Harry,

    Is this the problem?

    

    I don´t know how to solve that, sorry.

    Reply
15. Harry says:

    February 20, 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

    Reply
16. Jon says:

    March 13, 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)), "")

    Reply
17. Deepak says:

    January 30, 2013 at 10:06 pm

    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.

    Reply
18. Deepak says:

    January 30, 2013 at 10:32 pm

    Sorry, found exactly where I was going wrong.

    Reply