Formulas: Sort Order

    Thomach


    The item sample is in sheet 1
    This has a field called bin #
    Sheet 2 also has bin numbers and a sort order for each particular bin number. How do I merge these sheets so that the sort order on sheet 2 is maintained and sheet 1 is sorted according to sheet 2?


    thanks


    the actual item list is 8000 rows.

    brettnm,


    Not sure what you want the final output to look like?


    What I think could be a solution is to have a seperate Vlookup col using the bin# on sheet 1 to get the sort number on sheet2 and then sort the entire sheet1 using the vlookup col.


    regards
    Weasel

    I want the final output to be a list that looks like sheet 1 but is sorted by sheet 2. if there is no match then the row should be empty accept for the bin number.


    Please Help


    Thanks

    Brett,


    I used a vlookup solution to get what you want.


    1st: Cut the Bin#'s from sheet 1 and insert them so they are in Col A. I renamed the data on sheet1 MYDATA.


    2nd: Copy the headings on sheet 1 and paste them into row 1 of sheet 2.


    3rd: In B2 on sheet2 put the following formula -


    IF(ISERROR(VLOOKUP($A2,MYDATA,COLUMN(B1)))=TRUE,"",VLOOKUP($A2,MYDATA,COLUMN(B1)))


    4th: Drag this across and down to cover all cells required. (Calculation will take a while)


    last of all copy entire sheet2 and paste special/vlaues back over it to remove formulas (dramatically reduce file size)


    regards
    Weasel


    HOLD ON - doesnt work - will post back in 2 mins :)

    OK - Got it straight now.


    Have a look at the attached file. It uses just a small sample of your data, expand to suit.
    Once all vlookups have been done then copy/paste special/values to get rid of formulas.


    Regards
    Weasel

    Mr. Weasel,


    I'm using your formulas on the whole sheet and I am only picking up our original 5 samples. It is not picking up all the other items. What am I doing wrong?


    please help


    thanks

    DOH!!


    Sorry Brett,


    Neglegted to mention that the original data (sheet1) should be sorted by Col A - Lowest to Highest.


    Regards
    Weasel

    Bret,


    Not too sure here either. Please confirm that the formula you are using in cell B2 of sheet 2 is the one below:


    =IF(ISERROR(VLOOKUP($A2,MYDATA,COLUMN(B1),0))=TRUE,"",(VLOOKUP($A2,MYDATA,COLUMN(B1),0)))


    Where = MYDATA is a named range that covers all the data on sheet 1. If you do not use a named range then make sure that the range you use is absolute ie $A$1:$V$8000.


    Also you will need to highlight column A on sheet 1 and click the A-Z button in the Standard Toolbar, choosing to expand the selection when prompted.


    Regards
    Weasel

    I'm still having problems Mr. Weasel. I am copying the formula exaxtly but I am starting it in cell C2. The B column has the sort order number and is not empty in the brettnm spreadsheet you posted. I then coppied the formulas accross and down but got no match. I also don't want to sort by column A, I want to sort by the Column labeled sort order, (B). Can I email you the sheet I am trying to do? it is about 2.5 megs.


    thanks


    brett

    O.K. everyone. I have been having trouble with this one. On the attached sheet I need to sort all of the contents of sheet 1 by the order (column B) of sheet 2 over the common field (Bin Number) in both sheets. Thanks


    Thanks

    I'm having trouble understanding your insistence on "sorting over column B" in sheet 2. The order is the same as column A ... it's all sorted on Bin Number. Are you saying that you may arbitrarily change the order in column B so that the Bins might sort out of their current order, such as: A5, A3, A6, A8, A2, etc.? If so, I suggest that you show this in your example. And if not ... then what do you mean?


    What exactly is the point of Sheet2, other than to show a list of some bins that may not appear on Sheet1? (We can deal with that separately, I think.)


    Chris

    The bin numbers in column A are not always in alpha numeric order. In addition there may be bin numbers inserted in the future and that is why there is gaps in numbering in colunum B. I thought that made a diference and that is why the insistance on Column B. The need for sheet 2 is to put all of the items in sheet 1 in a certain order and insert that sort order number to each item where there is a match. This item list in sheet A that is 8000 rows long must be imported into a program in a certain order. I hope this makes more sense. thanks.

    Can I suggest that you provide a better example sheet and specification?


    You have all of your bins in order in Sheet2, and there are no gaps. Bins are numbered from 1 to 24, and sort order is 1 to 24. (In any case, gaps wouldn't matter if the sequence is still preserved. My bank statements come back each month with gaps in sequence where some checks have not yet cleared, but the check numbers themselves are in sequence for me to match in my register.) Do you REALLY intend to have bins out of alphanumeric sequence, or is it just gaps you're worried about? Please give examples of the data that we need to work with (and leave out the columns we don't need to work on). If we don't have to account for out-of-sequence bins then we'd be wasting time and processing power trying to deal with it.


    In addition, the data on Sheet1 also shows a one-for-one match with the bins on Sheet2. Presumably this will not happen with your live data, I understand. So why provide your sample that way?


    What do you want to have happen with the bins that aren't listed on Sheet2? Delete the rows? Segregate them on the same sheet? on another sheet? Hide them?


    Please think about the description and example of what you need. We're just guessing, otherwise.


    Chris


    PS: Sorry if I seem to be getting on your case here; it's been a long day and I've been dealing with a lot of poor specifications.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login