Sorting data to put "0's" last

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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
 
Hi Max,
Between you and another reply I have got it sorted so to speak. It works
great. Thanks so much
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
 
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.
 
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
 
-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?
 
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)
 
Hi Dave

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

A bit stumped??
 
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...
 
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
 
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...
 
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. :-)
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting in multiple worksheets 1
Tie additional cells to cell automatically updated 1
Columns not sorting properly. 2
Sort by Every Other Row? 0
Sorting rows of data of varying lengths 12
Sorting in Excel 2
Sorting 1
How to sort entire spreadsheet by one column sort 5

Back
Top