Sorting data to put "0's" last

S

Sue

I have a worksheet which has names paste linked from another workbook. The
rows all default to "0" til a name is filled in on the other workbook for
that cell. Not all the rows will end up with a name in them so when I go to
sort the worksheet once all names that are to be entered over time have been
done I get all the "O" rows at the top and then the names alphabetically
sorted after that. How can I get all the "0" rows to go to the bottom of the
sorted list.
 
B

Bryan Hessey

the easy way would be to use a helper column and enter

=IF(B1=0,"ZZZ",B1)

(assuming your name is in B1).

Then sort over the helper column etc (this can be a hidden column if
you wish)
 
M

Max

One play ..

Assume the linked names are in col A, A1 down

Use an adjacent helper col B

Put in B1: =IF(A1=0,REPT("z",10),A1)
Copy B1 down

Now sort both cols A & B using col B
 
S

Sue

Hi Max,
Between you and another reply I have got it sorted so to speak. It works
great. Thanks so much
Sue
 
S

Sue

Hi Bryan,
Thank you for your suggestion. It worked out just great and does what I
want it to do. Thanks so much
Sue
 
B

Bryan Hessey

Thanks for the response Sue, and the use of 3 Z's will ensure that names
beginning with Z will be sorted in their sequence.
 
B

Bryan Hessey

One point I meant to add was that in sorting you should select all dat
(to sort) and sort over column B, don't simply select columns A & B i
you have any related data in other columns
 
L

Lise

-Hi - was reading through the responses on this one and I have one question
please, I have two columns involved (last name first name) so changed the
formula a little to =IF(A168&B168=0,"ZZZ",A168&" "&B168) which does give me
last name space first name in the new column but when I then sort - all the
o's still go to the top rather than to the bottom - what have I done wrong
please?
 
D

Dave Peterson

Are you sorting by this helper column of formulas?

I don't see how this formula can return 0's.

Maybe you meant:

=if(a168+b168=0,"zzz",a168&" "&b168)
 
L

Lise

Hi Dave

Yes I am sorting by the helper column - tried your + and sadly no, this
returned #value.

A bit stumped??
 
D

Dave Peterson

What is in A168 and what is in B168?

Maybe you want:

=if(a168&b168="","zzz",a168&" "&b168)

But you didn't explain how you got 0's.
Hi Dave

Yes I am sorting by the helper column - tried your + and sadly no, this
returned #value.

A bit stumped??
Click to expand...
 
L

Lise

Hi Dave - Firstly thanks so much for your patience. Tried the latest and
still no joy.

Should have been clearer sorry - I have a spreadsheet of various info with
Column a Last Names and Column B First names

At the bottom of the sheet - when another sheet updates columns a and B auto
update BUT until they fill with actual data they show 0 which I have coloured
white text so they can't be seen.

When I do the formula for A and B into helper column C then sort the 0's go
to the top then the names sort alphabetically which is what I want just
without the 0's - maybe it can't be done.

Looking forward to your thoughts
 
D

Dave Peterson

One more maybe:

=if(a168&b168="00","zzz",a168&" "&b168)

I think that this is one of those things that can be done pretty easily. The
real difficult part is explaining the problem so that potential responders (ME!)
can understand it.
Hi Dave - Firstly thanks so much for your patience. Tried the latest and
still no joy.

Should have been clearer sorry - I have a spreadsheet of various info with
Column a Last Names and Column B First names

At the bottom of the sheet - when another sheet updates columns a and B auto
update BUT until they fill with actual data they show 0 which I have coloured
white text so they can't be seen.

When I do the formula for A and B into helper column C then sort the 0's go
to the top then the names sort alphabetically which is what I want just
without the 0's - maybe it can't be done.

Looking forward to your thoughts
Click to expand...
 
L

Lise

Dave - thank you so much you are a gem!! Yippee that one worked. Thanks for
your patience - rest assured I'll try to be more descriptive next time. :)
 
D

Dave Peterson

Glad you got it working.
Dave - thank you so much you are a gem!! Yippee that one worked. Thanks for
your patience - rest assured I'll try to be more descriptive next time. :)
Click to expand...
 
Top