Need to select earliest five rows matching highest value, and next highest if not enough

Hopefully the attachment will make everything clear.

Need to select five entries that were the "first to score" values in a range.

As shown on the attachment, it is straightforward if five or more entries achieve the Highest Score, Code needs to select the first five in the bloc, and copy and paste to a different location.

But if there are less than five, code should copy and paste however many there are, then find the bloc with the Second Highest scorers.

If there are more than the balance needed, copy however many are required, starting with the first match, and copy and paste them.

If there are less than the balance, copy and paste what there is, and look for Third Highest number. Start with the first match, and copy and paste them.

Initial thought was to use a formula in Col C to establish how many entries there are for each value, (=COUNTIF(B$1:B$21,B2) copied down),

Starting at row 21 and working upward, the Code could then use that as the "offset", to measure whether each value has more than the number of rows required?
And if there are more than five rows, it copies from the Offset cell down to the Offset,offset(5,0)

Please Login or Register  to view this content.

But the "options" I would have to build in suggest I am missing something much simpler.

Any suggestions and pokinters received gratefully as ever.

Ochimus

In your first sample in your spreadsheet, shouldn't Employee 5 be included?

Quite right, John, simplhy missed that!

But hopefully everyone can still understand the concept?

Not often I'm stumped completely at the outset, but I seemed to be looking at an infinite series of "IF THENs" even if I got the Code to sort out the relevant ranges, so I'm certain someone will have a "better mousetrap" to solve it..

Ochimus

cantosh,

Ingenious! I shall now lose ten nights' sleep working out how it does it, but it's exactly what I needed.

John, appreciate the approach as ever, but in this case having a formula solve it is ideal.

Ochimus

You're welcome. Glad too hear you have a solution that works for you.

I'm glad I could help. In case you need to make modifications, the rough breakdown from the inside out is as follows:

Whole Thing: =INDEX($I$1:$I$21,1/RIGHT(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)),LEN(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)))-1))

$J$2:$J$21+1/ROW($J$2:$J$21) takes the values in J and adds 1/ROW. This effectively breaks ties, giving primacy to the earlier entries when there's a tie.

LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)) The LARGE function gives us our top rank and paves the way to fill down 5 rows for the top 5. This gives us the top ranked J+1/ROW values, which is what you're after, but now we have to work back out of the ugly J+1/ROW decimals to more useful values that can be used to locate the associated employees.

RIGHT(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)),LEN(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)))-1) This looks busy, but all it does is remove the first digit of the value thus far, leaving us with 1/ROW. It dawns on me now that you could shorten this by simply subtracting J2:J21 instead of using RIGHT.

1/RIGHT(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)),LEN(LARGE($J$2:$J$21+1/ROW($J$2:$J$21),ROW(1:1)))-1) Brings us back to the Row number: 1/(1/ROW) = ROW. This portion of the formula alone returns the Row number of the (1st) largest value. The final step then just uses INDEX on column I to return the corresponding employee. It looks like a jumbled mess all together, but in actuality the formula just builds a tie-breaker to identify the desired values, then unravels it.

cantosh,

Attached should make the new question clear.

I23 - I27 shows the names from Col I matching the criteria in Col L perfectly (when the range is sorted by Col L).

But Col M has a separate sequence of numbers representing the Total activity from Day 1

As shown below, I tried to adapt your formula to match the Names in Col I to these new value:

{=INDEX($I$1:$I$21,1/RIGHT(LARGE($M$2:$M$21+1/ROW($M$2:$M$21),ROW()-22),LEN(LARGE($M$2:$M$21+1/ROW($M$2:$M$21),ROW()-22))-1))}

As you can see in K23 - K27 it says "0" in each case, and I'm not sure why it is not working?

Ochimus

Ah, I understand now. My original wasn't as versatile as it should have been, so it tripped over the multi-digit values. Try this one (it's still an array formula, so Ctrl + Shift + Enter):

=INDEX($I$1:$I$21,1/(LARGE($M$2:$M$21+1/ROW($M$2:$M$21),ROW(1:1))-INT(LARGE($M$2:$M$21+1/ROW($M$2:$M$21),ROW(1:1)))))

canmtosh,

Many thsnks, the tweak works perfectly.Another perpetual manual chore bites the dust!

Ochimus