| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
