Autofilter SORT that Ignores Forced Blanks ("")

[bryhogan]

Is there a way to get the Autofilter Sort function to ignore columns that have a forced blank ("") result in them? I'm trying to sort a column with a formula in each cell that results in either a % or a blank result (""). When I sort descending, Autofilter puts the blank results at the top of the list.

The ideal solution would be something that let me do this...

- if(isblank($A1),INSERTBLANK(),B1*C1)

vs.

- if(isblank($A1),"",B1*C1)

Any help would be greatly appreciatd!

[bryhogan]

Anybody? I'm a little desperate!

[Pete]

Instead of putting a blank in the cell, you should put a very small
number in there, one which is smaller than the smallest B1*C1 value -
as this is a %age then I would have thought that .000000001 would be
small enough!

When you sort it in descending order on this column these very small
values will appear at the bottom - you can easily highlight them all
and press <delete> to turn them into proper blanks for future work on
the sheet.

Hope this helps - I didn't see your earlier post as I was out.

Pete

[bryhogan]

Thanks, Pete. Unfortunately, I need the flexibility of sorting ascending or descending and not including those items. If I could find a way to get SORT to ignore those cells (as if they were truly blank), then that would work.

[Dave Peterson]

Maybe insert a new column that treats those "" cells the way you want.

=if(a2="",rept("z",255),a2)

(or something that sorts to the bottom)

Then sort on that column. (Hide/delete it when you're done.)

bryhogan wrote:
>
> Thanks, Pete. Unfortunately, I need the flexibility of sorting
> ascending or descending and not including those items. If I could find
> a way to get SORT to ignore those cells (as if they were truly blank),
> then that would work.
>
> --
> bryhogan
> ------------------------------------------------------------------------
> bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879
> View this thread: http://www.excelforum.com/showthread...hreadid=498680

--

Dave Peterson

[bryhogan]

Thanks, Dave. I'll try that...

[Kendra]

Dave,
I was reading through trying to see if my answer would be in here, and it
wasn't and of course, for some reason I can't post a new question.
My question does relate to sorting however -
I have columns that I need to sort ascending - but I do not want to include
any of the titles that I have put on the page, just the information under
their titles (for Example : one title is Region. I don't highlight that, just
the cells underneath, and when I hit the sort key, it automatically sorts the
title of the worksheet, as well as the title of the cell.This makes some of
the information above the title, and the rest below!) I hope this makes
sense. I am getting a little desperate. I have a project due in a few weeks
and it has to be perfect and I need to go over 24 months worth of data!!!
please help me!

"Dave Peterson" wrote:

> Maybe insert a new column that treats those "" cells the way you want.
>
> =if(a2="",rept("z",255),a2)
>
> (or something that sorts to the bottom)
>
> Then sort on that column. (Hide/delete it when you're done.)
>
> bryhogan wrote:
> >
> > Thanks, Pete. Unfortunately, I need the flexibility of sorting
> > ascending or descending and not including those items. If I could find
> > a way to get SORT to ignore those cells (as if they were truly blank),
> > then that would work.
> >
> > --
> > bryhogan
> > ------------------------------------------------------------------------
> > bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879
> > View this thread: http://www.excelforum.com/showthread...hreadid=498680
>
> --
>
> Dave Peterson
>

[Pete]

Kendra,

I have responded to your other post.

Pete