Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ranking & Sorting Groups (2k3)

    I'm wondering how i can sort a data set to give things priority using excel to increase our efficiency. in the attached example workbook i have a table which reflects an series of orders sorted by the time at which the orders were placed that need to be completed in a certain amount of time. to do this most efficiently, we need to group the similar order types so we aren't changing settings between every order. Then after they are grouped, they need to be sorted by quantity (descending) so we can process the biggest orders first unless there is an order with ASAP priority marked on it. in the ASAP case we'd like to do that order first AND the similar sizes to stay efficient, even if its overall a smaller quantity.

    The table on the right shows how this particular order should end up: grouped, sorted by quantity, and priority pushed to the top.
    Attached Files Attached Files
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking & Sorting Groups (2k3)

    I am a little confused by your order. based on the description I would have thought that you would sort like:
    <table border=1><td valign=bottom>Order</td><td valign=bottom>Type</td><td valign=bottom>Quantity</td><td valign=bottom>Priority</td><td align=center valign=bottom>9</td><td valign=bottom>3"</td><td align=right valign=bottom>300</td><td valign=bottom>ASAP</td><tr><td align=center valign=bottom>6</td><td valign=bottom>3"</td><td align=right valign=bottom>200</td><td align=right valign=bottom>

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking & Sorting Groups (2k3)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I didn't even sort it correctly when I did it manually!

    You got it correctly in your table, that's what i was going for.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking & Sorting Groups (2k3)

    In E1 enter a heading like "ASAP"
    In F1: a heading like: Total
    In G1: aheading like "AT"

    In E2 enter the array formula (confirm with ctrl-shift-enter)
    =COUNT(IF(($B$2:$B$10=B2)*($D$2:$D$10="ASAP"),1))

    In F2:
    =SUMIF($B$2:$B$10,B2,$C$2:$C$10)

    In G2:
    =E2+F2/(MAX($F$2:$F$10)+1)

    Copy E2:G2 to E3:G10

    E has the number of ASAPs for wach type. F has the total for each type. G has a "combined" ranking for each group (considers ASAP and Total)

    Sort A2:G10 (with Heading row) by
    AT, descending
    Type, descending
    quantity, descending.

    [If the totals of a group will never be the same as any other group (true for your data, but i would not presume it will always be true), you can eliminate column G and
    Sort A2:F10 (with Heading row) by
    ASAP, descending
    Total, descending
    quantity, descending.

    This relies on the "total" to define the "group" and so you don't have to combine 2 sorts into 1 column]

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking & Sorting Groups (2k3)

    I copied & pasted your forumlae in and for the like "ASAP" column, i get all 1's down the entire column. I think there might be an error in that formula, but i don't know what it is.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking & Sorting Groups (2k3)

    Another option if you want the "ASAP"s in a group to take precedence over the quantity you can add

    In H1 enter a heading like "AQ"

    In H2:
    =(D2="ASAP")+C2/(MAX($C$2:$C$10)+1)

    Copy H2 to H3:H10

    Sort A2:H10 (with Heading row) by
    AT, descending
    Type, descending
    AQ, descending.

    With this additional column, if order 6 had ASAP, instead of order 9, 6 would be done first, since the ASAP would "trump" the lower quantity. In the original, within a group the ASAP made no difference it was strictly by the descending quantity, regardless of the ASAP.


    This sorts:
    1) Groups with the most ASAP
    2) If the same number groups with higher totals first
    3) Then by the "type"
    4) then by ASAP
    5) then by quantity descending

    The original was:
    1) Groups with the most ASAP
    2) If the same number, groups with higher totals first
    3) Then by the "type"
    4) then by quantity descending


    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking & Sorting Groups (2k3)

    It is an array formula. Did you confirm with ctrl-shift-enter instead of just enter?

    [In the fomrula bar, they should have squiggly brackets around them ({})]

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking & Sorting Groups (2k3)

    Instead of the "Array formula" you could also use in E2:
    =SUMPRODUCT(($B$2:$B$10=B2)*($D$2:$D$10="ASAP"))

    This can be entered normally, it does not require ctrl-shift-enter.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking & Sorting Groups (2k3)

    ah that made it work - i thought i pressed it but maybe i wasn't doing it correctly. Thanks, Steve!

    now all i have to do is make it a macro <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •