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


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

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