| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sorting data to put "0's" last
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. |
|
#2
|
|||
|
|||
|
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) -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 |
|
#3
|
|||
|
|||
|
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sue" wrote in message ... 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. |
|
#4
|
|||
|
|||
|
Now sort both cols A & B using col B
should read as: Now sort both cols A & B using col B Ascending -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
|
#5
|
|||
|
|||
|
Hi Max,
Between you and another reply I have got it sorted so to speak. It works great. Thanks so much Sue "Max" wrote: 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sue" wrote in message ... 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. |
|
#6
|
|||
|
|||
|
Hi Bryan,
Thank you for your suggestion. It worked out just great and does what I want it to do. Thanks so much Sue "Bryan Hessey" wrote: 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) -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 |
|
#7
|
|||
|
|||
|
Glad to hear that!
Thanks for the closure .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sue" wrote in message ... Hi Max, Between you and another reply I have got it sorted so to speak. It works great. Thanks so much Sue |
|
#8
|
|||
|
|||
|
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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 |
|
#9
|
|||
|
|||
|
One point I meant to add was that in sorting you should select all data (to sort) and sort over column B, don't simply select columns A & B if you have any related data in other columns. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
-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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
Hi Dave
Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
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. Lise wrote: Hi Dave Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
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 -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: Hi Dave Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
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. Lise wrote: 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 -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: Hi Dave Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
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. :-) -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: 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 -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: Hi Dave Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting data to put "0's" last
Glad you got it working.
Lise wrote: 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. :-) -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: 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 -- Thanks Lise "Dave Peterson" wrote: 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. Lise wrote: Hi Dave Yes I am sorting by the helper column - tried your + and sadly no, this returned #value. A bit stumped?? -- Thanks Lise "Dave Peterson" wrote: 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) Lise wrote: -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? -- Thanks Lise "Bryan Hessey" wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=389531 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to maintain hyperlink when sorting data? | Excel Discussion (Misc queries) | |||
| Sorting Data from 2 sheets, one sheet which is already sorted | Excel Worksheet Functions | |||
| how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
| data sorting problem | Excel Worksheet Functions | |||
| Help in Sorting Data Please! | Excel Worksheet Functions | |||
Linear Mode
