Issues with sorting

I have been having some problems with sorting columns of numbers when the column includes blank cells. Some of the blank cells are there because nothing has been entered, while others are set to blank by a formula.
eg. in A1, =if(B1="","",B1). When I sort, some of the blanks appear above the numbers in the sort, others below.

This will also happen if I copy and Paste Special - Values. The blank cells in the new column all appear as if they have nothing in them, but the ones which originally came from formulae are sorted differently to the ones that were "real blanks".

Has anyone else experienced this, and does anyone know a way around it, so that all cells with no value in them are treated similarly by the sort?

Thanks

Matthew

I'd use another helper column:

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

Then sort by that.

(Or whatever character would sort the data the way you want.)

Matthew McManus wrote:
>
> I have been having some problems with sorting columns of numbers when
> the column includes blank cells. Some of the blank cells are there
> because nothing has been entered, while others are set to blank by a
> formula.
> eg. in A1, =if(B1="","",B1). When I sort, some of the blanks appear
> above the numbers in the sort, others below.
>
> This will also happen if I copy and Paste Special - Values. The blank
> cells in the new column all appear as if they have nothing in them, but
> the ones which originally came from formulae are sorted differently to
> the ones that were "real blanks".
>
> Has anyone else experienced this, and does anyone know a way around it,
> so that all cells with no value in them are treated similarly by the
> sort?
>
> Thanks
>
> Matthew
>
> --
> Matthew McManus
> ------------------------------------------------------------------------
> Matthew McManus's Profile: http://www.excelforum.com/member.php...fo&userid=5833
> View this thread: http://www.excelforum.com/showthread...hreadid=383031

--

Dave Peterson