 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Help With Sorting
Gudday to all XL gurus. I need some help on using the SORT function. I have a list of 100 competitors in a scoring spreadsheet that I hav written. I was trying to sort by surname (a-z). The problem is that this list i generated elsewhere and contains VLOOKUP functions. Obviously if i hav less than 100 competitors I end up with some cells appear blank bu contain VLOOKUP functions. XL sorts them first and I want them sorte last after the surnames (a-z). I cant add a helper column as this lis relates to over 48,000 forumulaes and functions that are already added If I add a helper column I have to re write all those formulaes. I tried adding a custom list in the tools - options - custom lists bu that still doesnt work. ANY HELP WOULD BE GREATLY APPRECIATED cheers and thanks guys/gals CHAPPO55 Hi I can't see why you would need to re-write all your formulae, but anyway a helper column can be placed anywhere on the sheet. Place it to the far right of your block of data, and assuming your column with the Names is column A, in this new helper column enter =IF(A2="",REPT("Z",255),A2) and copy down Any cells returning Null from your Vlookup's will be converted to a string of Z's. Now mark the whole block of data (including your new column to the far right) and sort ascending on the new column.  SignatureRegards Roger Govier > Gudday to all XL gurus. > [quoted text clipped - 20 lines] > cheers and thanks guys/gals > CHAPPO555 Thanks for that, unfortunatly I had thought of that option. The proble is that Column A is used as a source for over 20 other pages of data All of those pages rely on =if(isblank(name in co A),"****",vlookup,name in col A,one of 20 other sheets,other cell,row) (example used). If I change data in Column A to z rept, 255 then all of the other 2 sheets start looking for data which wont exist if there is NOT SUPPOSE to be a name allocated to that cell in Col A. If I use that forumula you described I will end up with #name or #erro values or I have to re write all of the other VLOOKUPS and thats nightmare because you cant succesfully cut and past VLOOKUP as the rely on different sort data and col number to find the data they ar after. Additionally I cant add a helper column to the right of my print dat as all of my other calculations I need for splitting ties etc are ther and they all have combination and absolute cell references combine along with some more VLOOKUP so they cant be moved so I have print dat I need to sort - then absolute data (cant be moved) then my helpe column that Id wish to sort by, so to select my sort area including th helper column I have to highlight the absolute data and when sort move it it would screw up my calculations. Reading this back sounds like I've painted myself into a corner doesn it !! Any help appreciated. cheers chapp Why doesn't a custom sort list work? It should, how are you applying it? SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Thanks for that, unfortunatly I had thought of that option. The problem > is that Column A is used as a source for over 20 other pages of data. [quoted text clipped - 24 lines] > > cheers chappo I don't think you understand exactly what Roger suggested! You're *not changing* anything in Column A. You're adding a (helper) column, in an out-of-the-way location, but still contiguous to the data, which configures *itself* according to the data in Column A, and then using *that* 'helper' column as the sort key. Do you follow? SignatureRegards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- Thanks for that, unfortunatly I had thought of that option. The problem is that Column A is used as a source for over 20 other pages of data. All of those pages rely on =if(isblank(name in col A),"****",vlookup,name in col A,one of 20 other sheets,other cell,row)) (example used). If I change data in Column A to z rept, 255 then all of the other 20 sheets start looking for data which wont exist if there is NOT SUPPOSED to be a name allocated to that cell in Col A. If I use that forumula you described I will end up with #name or #error values or I have to re write all of the other VLOOKUPS and thats a nightmare because you cant succesfully cut and past VLOOKUP as they rely on different sort data and col number to find the data they are after. Additionally I cant add a helper column to the right of my print data as all of my other calculations I need for splitting ties etc are there and they all have combination and absolute cell references combined along with some more VLOOKUP so they cant be moved so I have print data I need to sort - then absolute data (cant be moved) then my helper column that Id wish to sort by, so to select my sort area including the helper column I have to highlight the absolute data and when sort moves it it would screw up my calculations. Reading this back sounds like I've painted myself into a corner doesnt it !! Any help appreciated. cheers chappo Signaturechappo555 ------------------------------------------------------------------------ chappo555's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36060 View this thread: http://www.excelforum.com/showthread.php?threadid=559598 Thanks for the replies but still cant figure it out Nick HODGE - In answer to your question: I am trying to record a macro (im very basic with macros) and selec the data, sort, first key order, change to custom list that I hav added through tools, options, custom lists. I tried a custom list of just a to z but that didnt work and then tried reversing the default sort order under help seach ie a to z an then all the other characters. NEITHER seems to work despite my efforts. I am trying to sort alphabetically but all the surnames come from othe sheets and some will appear blank (depending on the number o competitors that I actually have ranging from 5 to 100) but these cell contain forumulaes. In answer to the other answer. If contiguous means that it must b attached to the data, ie my data goes from Col A to Col AT (which i does), I already have helper colums in AU to ZC inclusive ( all thes are if(isblank(xx),"**",vlookup etc. So I cant add a col at AT and I cant insert one at Col A as it wil stuff up over 19,000 formulaes that I already have cheers chappo555 To get a custom sort to work, you should rearrange your 100 names in to the order you want and then add that list to the custom sort. Then when you use the custom sort it will sort in the same order as your preset list e.g a list loaded as a custom one like so Nick Hodge Chip Pearson John Walkenbach Bob Phillips Gord Dibben would sort a list like this Nick Hodge Chip Pearson Gord Dibben Nick Hodge Bob Phillips John Walkenbach Bob Phillips Like this Nick Hodge Nick Hodge Chip Pearson John Walkenbach Bob Phillips Bob Phillips Gord Dibben SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Thanks for the replies but still cant figure it out > [quoted text clipped - 24 lines] > cheers > chappo555. If the OP's problem is that blanks sort to the top, I don't think that a custom list will help. > To get a custom sort to work, you should rearrange your 100 names in to the > order you want and then add that list to the custom sort. Then when you use [quoted text clipped - 27 lines] > Bob Phillips > Gord Dibben SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html Debra I missed the blanks bit as it appeared originally that he just wanted his names sorted by last name. It is still confusing why he can't use a helper column also SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > If the OP's problem is that blanks sort to the top, I don't think that a > custom list will help. [quoted text clipped - 31 lines] >> Bob Phillips >> Gord Dibben Hi If you are at column ZC, then you must be using XP2007. Since that ends at column XFD, why can't you add a column at ZD? SignatureRegards Roger Govier > Thanks for the replies but still cant figure it out > [quoted text clipped - 26 lines] > cheers > chappo555. Contiguous means it has to be part of the same table (no blank column or row separating it), but it doesn't have to be adjacent to the section with the data. It could be added to the far right of the table, in column ZD, as Roger suggested. > Thanks for the replies but still cant figure it out > [quoted text clipped - 24 lines] > cheers > chappo555. SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.