sorting/puting items in a table based on the values from another table

**dhrddm** · 01-28-2011, 07:44 AM

Hi.

I want to sort in a table the high runners and the low runners. All items that are being produced more than 500 pcs per week should go in the high runners table and all that don't, in the low runners table? How can i make that in Excel?

See attachment for experimenting and exemplification of my problem.
Thank you in advance.

**Blake 7** · 01-28-2011, 07:58 AM

Do cols b, c d, e and f represent weeks?

is the data in col a a list of runners?

for example ---- is 305683720162-AC

a high runner in week one and a low runner in week two?

You need to explain your data a bit better

**dhrddm** · 01-31-2011, 03:03 AM

days, not weeks. And yes, 305683720162-AC is a runner.

**Blake 7** · 01-31-2011, 09:01 AM

I'm sure there is a better way of doing this using sumproduct with if but i dont know how to.... so im calling in the cavalry! however, this solution should work.

In G1 enter

=SUM(IF($A$1:$A$24=A1,$B$1:$F$24)) hit CSE

in col H

=IF(G1>500,A1,"") (list of high runners)

In Col I

=IF(G1<500,A1,"") (list of low runners)

Hope this helps

**NBVC** · 01-31-2011, 09:27 AM

You can perhaps add a helper column... so in G1:

Please Login or Register  to view this content.

copied down

Then, in K4 add a count:

Please Login or Register  to view this content.

in K14:

Please Login or Register  to view this content.

(this assumes you don't want the 0 runners.. if you do remove the second COUNTIF)

then in L5:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

in L12:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

**Blake 7** · 01-31-2011, 09:44 AM

N - thanks so much for looking at this.

Dont know why I thought that a sumproduct would work on this, guess i dont understand SP v well!!

I will now study your option!

All the best..........

BTW are you on the list?

https://mvp.support.microsoft.com/co...mpetency=Excel

I used the link you sent! thanks

**NBVC** · 01-31-2011, 09:58 AM

Blake, I sent you a PM link to my profile.

**dhrddm** · 02-01-2011, 05:51 AM

i marked this as solved because it is nearly solved. There is still a little error in the formula it seems...
i put 2000 from A1 to A5 so that they will be recognized as high runners and in A6 i put 1 so that A6 will be recognized as low runner. In fact in the high runner list got A1:A5 (as wished) but in the low runner list A6 didn't made it... Instead, in the list of low runners went A1 (!!!) which clearly is no low runner... What happened?

**Blake 7** · 02-01-2011, 06:14 AM

As you marked the thread as solved there is a possibility that N (who wrote the formula) and other people will skip this post.

May I suggest that you start a new thread and attach the spreadsheet.

**NBVC** · 02-01-2011, 08:47 AM

It is column G that is looked at in my formulas (i.e. the sums of columns B:F) When I made it so the sums of G1:G5 where each 2000 and the sum of G6 was 1, it works in both tables as expected. See attached.

Blake 7, marking the thread Solved doesn't deter us from returning to the thread. Most of us maintain our subscribed status to the thread and will know when a thread has been continued, whether previously marked solved or not. If after a certain amount of time (a day or so) no response received, then you can either bump and try again or start a new thread with a link to the solved thread for continuity.