Sorting Blank Values

The Wolf

New Member
Joined
Aug 1, 2002
Messages
6
I have a worksheet with 5000 rows of formulas. The formula in column A is:

=IF(Sheet1!A9="","",Sheet1!A9)

The value in Sheet1!A9 is either a number or there is no value in the cell. The problem occurs when I sort on this column. If I sort descending, all of the blank values are at the top of the list, then followed by numbers. I always want the blanks to be at the bottom, but I still need the ability to sort either ascending or descending.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Blank cells always come last but the trouble is "" is not the same as blank.

You may be able to create a dynamically sorted list using the LARGE function. What range of data are you referencing and where are you putting the results you want sorted?
 

The Wolf

New Member
Joined
Aug 1, 2002
Messages
6
On 2002-09-16 08:53, Andrew Poulsom wrote:
Blank cells always come last but the trouble is "" is not the same as blank.

You may be able to create a dynamically sorted list using the LARGE function. What range of data are you referencing and where are you putting the results you want sorted?

In sheet 1, the data range is variable (the number for rows could be 100, or they could be 1000, as it changes daily). Therefore, to compensate for the variability, I entered the formula I referenced earlier from cell a2 to a3000 in sheet 2. I need to be able to sort on this column in sheet 2. There are other columns of data in sheet 2 as well, which I will also use to sort this data.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Enter this formula in cell A2 on Sheet2:

=IF(ISERROR(LARGE(Sheet1!$A:$A,ROW(A1))),"",LARGE(Sheet1!$A:$A,ROW(A1)))

and copy down. It assumes column A on contains numbers or "".

No more sorting!
 

Forum statistics

Threads
1,144,741
Messages
5,726,010
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top