| Forum Home | ||||
| PC World Chat | ||||
| Thread ID: 116459 | 2011-03-04 22:10:00 | Paging our XL Experts | B.M. (505) | PC World Chat |
| Post ID | Timestamp | Content | User | ||
| 1183640 | 2011-03-04 22:10:00 | This question is about how to get XL to sort all blank cells to the bottom not the top. Ok, I start with image 1. Ask XL to sort on column "C" in decending order and get image 2. I can't for the life of me find how to fix the problem. I've tried on different computers with different versions of XL but same problem. (XL 2002 & 2000) |
B.M. (505) | ||
| 1183641 | 2011-03-04 22:35:00 | Are those horses names B.M.? Running a tote, are we?? :D I don't think Excel before version 2007 will be able to sort the way you want. If you're using 2007 or 2010, you could colour the empty cells using the Fill function, then apply a sort as below. Once the empty ones are where you want them, you could re-sort what's left, if required |
johcar (6283) | ||
| 1183642 | 2011-03-04 22:36:00 | This question is about how to get XL to sort all blank cells to the bottom not the top. Ok, I start with image 1. Ask XL to sort on column "C" in decending order and get image 2. I can't for the life of me find how to fix the problem. I've tried on different computers with different versions of XL but same problem. (XL 2002 & 2000) For 2007, I would do it this way: Custom sort Add Level Sort by Column C sort on values, smallest to largest Then by Column B sort on values, A - Z This will give you a table sorted by $ amount (Column C), then push zero amounts to the bottom. Is this what you need? |
WalOne (4202) | ||
| 1183643 | 2011-03-04 22:37:00 | Zero is not the same as NULL.... | johcar (6283) | ||
| 1183644 | 2011-03-04 22:41:00 | Johcar, I think 2003 will do the same thing, but it's slightly different sort command. I.e. Sort by Column C Descending then by Column B. Same thing except there is no "Level" to be added. Sorting by Column B is essential if we are to move negative values to the bottom of the column. :confused: | WalOne (4202) | ||
| 1183645 | 2011-03-04 22:47:00 | I think that Excel doesn't include blank cells natively in the sort because usually a user would not want to see them.... It's been a while since I used 2003, so I will bow to your obvious familiarity with this version... :) |
johcar (6283) | ||
| 1183646 | 2011-03-04 22:47:00 | "This will give you a table sorted by $ amount (Column C), then push zero amounts to the bottom or negative values to the bottom of the column". OK, push null or negative amounts, values to the bottom. Still has the same desired outcome and the horses won't care. :p |
WalOne (4202) | ||
| 1183647 | 2011-03-05 02:31:00 | You want a bet Johcar? :D I give you fair warning, the race was run last week and I know the winner. ;) Anyway, I’ve tried your suggestions without any luck. I don’t think XL 2000 or 2002 has a sort on colour facility but I stand to be corrected on that one. As for sorting on Column C and then B, well that just shuffled things into a sort of random order. But here’s the twist. I’ve had Open Office for ages and never used it, so I opened the spreadsheet with that which was no problem, then asked it to sort as I did XL and bingo, it sorted perfectly. :thumbs: So, if Open Office can do a simple thing like sorting and ignoring blank cells and XL can’t, then I don’t mind using it. However, does Open Office have a Web Query facility? I haven’t found it yet if it does. (Got to follow the Tote Johcar) ;) |
B.M. (505) | ||
| 1183648 | 2011-03-05 05:37:00 | Sort by cell colour is an Excel 2007/2010 improvement. Well done on finding that Open Office can resolve your issue - and good luck with the horses!! |
johcar (6283) | ||
| 1 | |||||