Sorting numbers and text cells also removing blanks using an array formula
Question: How do I sort text and numbers and also removing blanks using an array formula?
Answer:
Array formula in B2:B15:
Recommended articles:
Filter a unique distinct list and remove blanks
Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several [โฆ]Comments(24) Filed in category: Excel, Unique distinct values
Extract a unique distinct list sorted alphabetically removing blanks from a range
This is an answer to a question in this blog post: Extract a unique distinct list sorted from AZ from [โฆ]Comments(21) Filed in category: Excel
Comments(81) Filed in category: Excel, Sort values
Create a unique distinct alphabetically sorted list extracted from a column
The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique [โฆ]Comments(53) Filed in category: Excel, Unique distinct values
Extract a list of alphabetically sorted duplicates from a column
Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula [โฆ]Comments(1) Filed in category: Duplicate values, Excel, Sort values
How to enter an array formula
 Copy array formula (Ctrl + c)
 Select cell B2

Click in formula bar
 Paste formula (Ctrl + v)
 Press and hold CTRL + SHIFT
 Press Enter
If you did this correctly, the array formula now begins with a { and ends with a }.
How to copy array formula
 Select cell B2
 Copy cell B2 (Ctrl + C)
 Select cell range B3:B14
 Paste (Ctrl + v)
Download excel example filesortnumbersandtextcellsusingexcelarrayformulav2.xls
(Excel 972003 Workbook *.xls)
Functions used in this blog post:
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
SMALL(array,k)
Returns the kth smallest number in this data set.
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
This blog article is one out of six articles on the same subject.

Sorting text cells using array formula 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
 Sort text cells alphabetically from two columns using excel array formula
 Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
 Sort a range from A to Z using array formula in excel
Read more related articles
Category: Excel
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The [โฆ]Comments(249) Filed in category: Concatenate, Excel, Textjoin
Comments(161) Filed in category: Charts, Excel, Interactive
22 Responses to โSorting numbers and text cells also removing blanks using an array formulaโ
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
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
Okay here's what I need to do:
I have 5 lists each with somewhere between 10 to 30 text values associated with a title of that list. On another worksheet I have 2 dropdown lists with the 5 list names in each one. You may choose one of the names or two. I want another worksheet (based on those choices) to return all matching results and place them in a dropdown list, I need it to update if you change the choices at any time.
Does anyone know how to do this? I've been able to figure out alot of things but this one I can't clearly find out how to accomplish. I can and have used VBA on this sheet to accomplish other things but I'm not proficient in writing the code from scratch as I'm still learning it.
I don't see in your page my problem. I have a matrix 5x100 I tried wiht datasort..etc. I need to sort by row, nothing can be change by columnns, so datasort change one row, when I tried the next, the first one come back to the original desorger, how can I change the whole matrix in one time by rows not by columns. example.
98765
65321
9887542132 etc.
thank you for any help.
baum schausberger,
Excel 2007:
1. Select cell range
2. Right click on cell range
3. Click Sort...
4. Click Custom sort
5. Click options...
6. Select Orientation: Left to right
7. Click OK
8. Select a row
9. Click OK
This doesn't work correctly with text starting with an equal sign:
(Yes, I know it's a pathological case)
1) Enter some normal data in the List range
2) Enter a text string starting with a single quote and an equal sign (or, format a cell as Text and put a formula in it)
3) notice duplicate entries in the output not present in the input
List Sorted
a 1
aaa 2
1 9
2 10
b 11
777 777
9 1E+88
c a
10 aaa
11 aaa
1.00E+88 b
=A17+1 c
e e
g f
f g
here's the same data, but easier to read:
List
a
aaa
1
2
b
777
9
c
10
11
1.00E+88
=A17+1
e
g
f
Sorted
1
2
9
10
11
777
1E+88
a
aaa
aaa
b
c
e
f
g
Note the duplication of the aaa entry, and the noninclusion of the text entry starting with =
Also, the formula failed to work with the entire text of the United States Declaration of Independence in a single input cell. It just puts #VALUE in every output :)
and for shorter multiline inputs, it concatenates them:
'ab'
becomes
'ab'
although 'ab' becomes 'ab', it's still sorted differently. The comment form isn't allowing me to correctly format what I'm trying to write.
Brian Minton,
This is what I get:
Hi Osacar,
Thanks for sharing this woderful formulas.
Is there anyway we can avoid the "#NUM!" and get black instead of that?
Manu,
=IFERROR(INDEX($A$2:$A$15, MATCH(SMALL(IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), ROW(A1)), IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), 0)),"")
Is there a limit to the number of cells this will work for? I'm using excel 2003 and there seems to be a limit of 25 cells for sorting. Anything more and it starts to add duplicates.
I have an array sheet that pulls 50 cells from a data dump based on the date. I'm using an "INDEXSMALLROW" array function to pull based on the date, but the data is not sorted. The number of entries changes every day, but is less than 50. Then I use the above formula to sort the data for output to my report page.
Is there any way to extend this formula to be applied to more cells?
Buffalo Bill,
This is an old article, I made a new formula and uploaded a new file.
I am sure the old formula could handle more than 25 cells, it really depends on your computer hardware.
If you canยดt get it working, upload your workbook and Iยดll see what I can do.
This, along with the related formulas, is pure genius! I tested it on sample data, and I am sure I can put this to use in the spreadsheet I am working on.
Thank you!
~ Jere
J Becker,
thank you!
how to sort if there are dates? Lets say in format: 14.03.1999
Your old formula removes duplicates, this one seems to not.
thanks
Fantastic site.
I am using Excel 2013 and the formula worked great except for one small problem. I am using a spread of 150 cells (to allow for the list to grow) It sorted my data as shown:
#180
#181
#250
#275
1462
1468
1469
1471
...
The problem I am having is that it stacked a hundred blank cells above it rather than ignoring them like I would prefer. What am I missing?
[โฆ] PS. For a background, I ran into this problem when I tried to build a formula that would both extract unique values from a list with possible duplicates, and sort the unique values in numerical/alphabetical order. My current solution is to do this in two steps. One solution for how to do it in one step is proposed here. [โฆ]
Hi, Is there any way to extend the above sorting formula for cell range A2 to A200 or more. need it urgently please
Have you tried changing the A$15 to A$200?
=INDEX($A$2:$A$200, MATCH(SMALL(IF(ISBLANK($A$2:$A$200), "", IF(ISNUMBER($A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200)+SUM(IF(ISNUMBER($A$2:$A$200), 1, 0))+1)), ROW(A1)), IF(ISBLANK($A$2:$A$200), "", IF(ISNUMBER($A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200)+SUM(IF(ISNUMBER($A$2:$A$200), 1, 0))+1)), 0))
Yes, I tried. but its not taking. the cells are becoming blank otherwise showing N/A
Hi, Now its working but the empty cells are showing first and then the sorted data. But i dont want the cells which are blank between A1 to A200. I want the data to be displayed from column A2 itself. Please help