Create Top Ten List Based On Number Of Classes Currently Overdue

[petermichael]

OK, I've got a big sheet with about 180 members and their status in roughly 30 classes.

I have all those that are overdue auto colored (entire row) in red.

I want to take a section of the worksheet, or perhaps another worksheet, and make a top 10 list that reports the worst classes of the bunch in terms of quantity overdue.

So column G has the course name, and column J has the status, with a specific text of "*OVDUE" if the course is overdue.

here is what I am envisioning will accomplish the task -

1. I want to count the number of overdues, per each course and chart that somewhere.
2. I want to then make that chart actively sorted, putting the highest counting courses on top.
3. On my main worksheet, I will simply put a block of 10 rows and have them just directly pull the names from whatever is on the top of that auto sorting list.

I guess my main hang up in the process is the auto sorting of the list. Is it possible?

[dredwolf]

A sample/example workbook would help a lot here, without seeing the formats and data, this is not easy to give you an answer

Edit-
AND expected output of at least a few calculations

[petermichael]

Here is a sample file.
I would like a list that doesn't just total up the total of classes in overdue status, like, for instance telling me the the class, "exceptional release" has 3 in overdue.

I need the list to sort itself based on quantity, so that the class with the greatest number will rank the highest.

so output from this chart would be

ccode title quantity
002124 exceptional release 3
002093 auth sign all cond tags 2
018078 dad info assure aware 1
002099 auth sign nrts all 1

then I could know at a glance what to concentrate on from a chart with thousands of lines.

[petermichael]

Anyone? This has to have far reaching applications and I know it's possible.
I'll go $10 on paypal for it.

[micope21]

Hi

Please click on attachment. Is that what you looking for?

[petermichael]

Nope, but a pretty cool list.

I need a list of the top 10 CLASSES, that are overdue.

So as I wrote above,

The output would be
ccode..........title.............................quantity
002124........exceptional release.............3
002093........auth sign all cond tags........2
018078........dad info assure aware.........1
002099........auth sign nrts all................1

This allows us to see what classes we need to prioritize.

[micope21]

Ok

Try this one

[petermichael]

Man, that is cool.
Almost there!
Now how do I get it to minimize down to a single row per class.
I've got a dozen classes that are over 20 overdues, leaving me sorting like I already do.

This being on a separate worksheet to tabulate everything - that's just fine, but on the main page, I have 10 rows set aside for the top 10 list, and all I need are column F,G, and M, with a single row for each distinct class.

I guess just a code to go down Column F, and take the first 10 distinct values, and the corresponding F, and M value for each.

[micope21]

I'm not sure what you mean!. Could you put in sample file?. That will give me idea what you lookiong for.

[petermichael]

desired result.xlsx

Here's what I'm after.

[micope21]

Hi

Sorry! It a bit late due away travel 3 day work.

[petermichael]

well I know we are getting closer. still some odd things happening.

Let's start with the "CON" worksheet, Column M.

if you simply copy/paste the *OVDUE value into any cell in column J, it does not return an accurate number in column M. For instanct if you copy/paste *OVDUE into cell J12, it does not change the corresponding M column values to 4. Also, if you do the same thing into cell J38, it will give you a "0" in cell M38, when it should be a 1.

what got me excited was the fact that some of the changes mentioned above did cause changes to the other worksheet, and I can understand some of what you did, it just doesn't work yet.

but I appreciate the effort, and my mouth is watering, so I'm upping the solution reward to $20

[micope21]

Ok. Yes it can be done only problem with array formula was
EXCEPTIONAL RELEASE 3
AUTH SIGN ALL CONS TAGS 2
AUTH SIGN NRTS ALL 1
DOD INFO ASSURE AWARE 1
It only pick up 3 2 and 1 But not both 1

Only way will work if you do this
EXCEPTIONAL RELEASE 4
AUTH SIGN ALL CONS TAGS 3
AUTH SIGN NRTS ALL 2
DOD INFO ASSURE AWARE 1

Have look attachment file?
You see what I mean.

[petermichael]

Oh so close.

I like that the 2nd worksheet lists all overdue classes. I just need the last 2 parts.

1. The numbers on the right hand side (cell H19-H28) need to accurately update to the total of overdues for the classes.

2. The chart needs to put the biggest number on top, smallest on the bottom.

Thanks for the efforts!

[micope21]

Got it what you looking for.

Attachment

[petermichael]

Incorporated and working (on a very big list)
Thanks a bunch, please PM me your info.