# Thread: Ranking & Sorting Groups (2k3)

1. ## 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.

2. ## 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. ## 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.

4. ## 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. ## 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.

6. ## 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. ## 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. ## 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. ## 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>

#### Posting Permissions

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