Sorting with blanks

[slam]

I have a column with a formula that returns either a number, text, or displays a blank using a formula that starts with =IF(ISBLANK(B3),””,

How do I sort on this column so the numbers are sorted in descending order, followed by the text, followed by the blanks when ISBLANK is true.

For instance, its currently sorting as follows:

DNS
[Blank]
[Blank]
[Blank]
25
18
15
0


I wanted it to sort as:
25
18
15
0
DNS
[Blank]
[Blank]
[Blank]

 

[circledchicken]

Hi,

Here's one option:

Assume your data is in cells A1-A8.

• Enter the following formula in cell B1 and drag down to B8 (this is an array formula so use CTRL-SHIFT-ENTER rather than just ENTER):
  =IF(A1="",1E+307,IF(ISNUMBER(A1),COUNTIF($A$1:$A$8,">="&A1),COUNTIF($A$1:$A$8,">="&A1)+MAX(ISNUMBER($A$1:$A$8)*(COUNTIF($A$1:$A$8,">="&$A$1:$A$8)))))
  
  This function assigns a rank to each item in your list - blanks are given an arbitrary large rank so they end up at the bottom. The Maximum number rank is added to each letter rank to ensure the letters follow the numbers.

• Enter the following formula in cell C1 and drag down to C8:
  =INDEX($A$1:$A$8,MATCH(SMALL($B$1:$B$8,ROWS($C$1:$C1)),$B$1:$B$8,0))
  
  C1-C8 will now be a dynamically sorted list (numbers in descending order, followed by text in descending order, followed by blanks).

Alternatively, you can also sort by Column B manually if you didn't want the dynamic sorting.

My source for learning how to sort text in this way is this very nice article - http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

 

[slam]

I gave this a try, but am struggling with it a bit. I also took a look over the webpage you linked. I wonder if my problem is that not all my values are unique?

In X3 I have the following formula:
=IF(ISBLANK(B3),"",SUM(E3:W3))

In Y3 I have the following formula:
=IF(X3="",1E+307,IF(ISNUMBER(X3),COUNTIF($X$3:$X$37,">="&X3),COUNTIF($X$3:$X$37,">="&X3)+MAX(ISNUMBER($X$3:$X$37)*(COUNTIF($X$3:$X$37,">="&$X$3:$X$37)))))

In Z3 I have the following formula:
=INDEX($X$3:$X$37,MATCH(SMALL($Y$3:$Y$37,ROWS($Z$3:$Z3)),$Y$3:$Y$37,0))

My data in X3 to X37 includes nine unique numbers, two 0's, and the rest are blank because B3 is blank.

 

[slam]

Sorry, this is doing my head in! I'm going to add something relevant later once I get my head around it.

 

[circledchicken]

Hi again,

The values not being unique shouldn't be a problem. The only other thing I can think of is to ensure you use CTRL-SHIFT-ENTER for the formula in Y3.

If you want to post your data as it's laid out / a sample here I'm happy to have a look if it helps.

 

[slam]

Ok, I've left the 2nd formula out to simplify things.

If I do an Ascending Sort, it works. It'll sort like:

1
2
3
4
5
6
7
8
9
10
13
13
13
1E+307
1E+307
1E+307

However, for a Descending Sort (which I need), the blanks (1E+307) are of course at the beginning. What I need is to sort descending, but with the blanks at the end, meaning I want it to sort like:

13
13
13
10
9
8
7
6
5
4
3
2
1
1E+307
1E+307
1E+307

How do I go about this?

Incidentally, for the non unique values (where 13 is displayed 3 times), I've used the formula a 2nd time to sort by a secondary column.

 

[circledchicken]

Hi,

Sorry, I'm a bit confused - for the sample you posted initially, it works for me (including handling unique values). Are you sure all your numbers are formatted as numbers and text as text and blanks as "" ?

Maybe post the complete dataset your trying to sort and I'll have a look?

 

[slam]

Hi,

Sorry, I'm a bit confused - for the sample you posted initially, it works for me (including handling unique values). Are you sure all your numbers are formatted as numbers and text as text and blanks as "" ?

Maybe post the complete dataset your trying to sort and I'll have a look?

I don't know how I'd individually format the cells when the data is dynamic and it can contain numbers or text from formulas, or blanks from a "" formula.

 

[circledchicken]

I don't know how I'd individually format the cells when the data is dynamic and it can contain numbers or text from formulas, or blanks from a "" formula.

I don't think you need to, if the format is set to General you should be fine, but I'm afraid I'm not sure how else to help without looking at the data.

 

[slam]

Ignore the PM - this works! I was just making a really silly mistake.

Thanks so much!