I am working on a spreadsheet with close to 300 shipping lanes and about 10 carriers per lane. I have succeeded in ranking each lane by the lowest to highest freight cost; however, now I need to figure out how to override the rank so that I can designate a preferred carrier for a rank position (1 thru 3). If I designate a rank for a carrrier, the other ranks need to recalculate to the next position. Can someone tell me if this can be down and how? I need this to wrap up a bid project, so any help would be greatly appreciated.
The formula I am using to rank is: =SUMPRODUCT(--($A$2:$A$39=A2),-- (F2>$F$2:$F$39-G2))+1 Sample spreadsheet is provided below. Thanks COLUMNS: A = MSL_ID*, B = CARRIER, C = TOTAL FREIGHT, D = Override of Rank(Preferred Carriers), E = CURRENT RANK, F = RANK THAT I WANT A B C D E F 21075-01022 ABC 855.64 1 4 21075-01022 CDE 875.61 1 2 1 21075-01022 EFG 910.68 2 3 2 21075-01022 DEF 1040.64 4 5 21075-01022 BCD 1100.61 3 5 3 21075-01022 AFN 1157.76 6 7 21075-01022 FGH 1257.64 7 8 21075-02702 FGH 1006.24 1 1 21075-02702 CDE 1032.53 2 2 21075-02702 ABC 1033.165 3 3 21075-02702 EFG 1107.63 4 4 21075-02702 BCD 1191.65 5 5 21075-02702 DEF 1241.39 6 6 21075-02702 AFN 1264.57 7 7 21075-04106 CDE 1321.37 2 1 2 21075-04106 BCD 1367.37 3 2 3 21075-04106 ABC 1411.885 1 3 1 21075-04106 EFG 1427.36 4 4 21075-04106 FGH 1456.24 5 5 21075-04106 AFN 1551.9 6 6 21075-04106 DEF 1570.69 7 7 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
