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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,181,684
Messages
5,931,412
Members
436,788
Latest member
Oteez

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