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
Related blog posts
- Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
- Sorting numbers and text cells also removing blanks using array formula in excel
- Sorting numbers and text cells descending also removing blanks using array formula in excel
- Create unique distinct list sorted based on text length using array formula in excel
- How to automatically create a unique distinct list and remove blanks
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:
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)