# Sorting Blank Values

#### The Wolf

##### New Member
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?

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.

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!

Replies
2
Views
78
Replies
1
Views
566
Replies
17
Views
671
Replies
1
Views
360
Replies
2
Views
277

1,219,574
Messages
6,149,084
Members
450,855
Latest member
onecodevee01

### 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.

### Which adblocker are you using?

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

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