Rank Duplicates Based on Other Cell Value

D

Drogan

New Member
Joined
Mar 1, 2011
Messages
32
I have a score sheet that works perfectly except for one thing: I can't for my life figure out how to rank duplicates based on the highest dropped score (5 scores, highest and lowest are dropped), and there are 10 scores total with 3 rounds: top 6 in 2nd round, then top 4 from 2nd round in 3rd round:

R1: 10 (all scores)
R2: Top 6 from R1
R3: Top 4 from R2

I would need the scores in order from first to last based on the summed score, but any duplicates in order based on highest dropped value.

I would need the top 6 total scores to be timed in order for the 2nd round. So say score 3 and 4 are equal, but then score 4 has the higher dropped value, score 4 would need to be timed before score 3.

Then from there the top 4 scores timed in order for the 3rd round.

I would also need the combined (overall total) of each set of scores to be in order also based on highest dropped score.

All the scores are put on sheet 2 as the rounds progress (so round 1 scores as they're scored, then round 2 as they're scored, then round 3 as they're scored -- so it automatically updates with each score).

I have included a link to an example document (since I can't upload attachments here) to [hopefully] make sense of all this. It's hard to describe properly without seeing an example.

So on sheet 1 in the Time slots you will see 1st, 2nd, etc., which is the order they need to go in to be timed for the next round. Then round 3, 1st, 2nd, 3rd, 4th is the order the final scores should be in.

On sheet 2 are the names and scores in the order they're supposed to be taking into account the highest dropped score of any duplicate, both "clean" and combined.

Please be aware, however, that the judged scores may not always be in order from lowest to highest. I just did it that way on the example sheet as that's how we've been doing the scoring.

The top 10, top 6, and top 4 lists (message box script) are taken from sheet 2.

I have included the timer I use as well. I removed the Penalty formulas and the formulas I had on sheet 2 for the example. I left the code in tact that I used to determine largest score if there are no duplicates to move on to the next rounds and time in order.

So like I said, everything works perfectly on my actual score sheet with the current formulas and scripts I have, except I can't figure out how to rank duplicate scores and use those scores accordingly to determine order in which they need to be timed and added to sheet 2.

I really hope this makes sense with the document as the example.

Link: http://www.mediafire.com/?o2w4qwdefqzmz8f
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Most forum users, myself included, will not download files posted by others, if you need to post data samples it should be done with html maker, excel jeanie, or by copy / paste from your worksheet (apply 'All borders' first if you use this method).

Based on your description of what you need

=RANK(A2,$A$2:$A$11)+SUMPRODUCT(--($A$2:$A$11 = $A2),--($B2 > $B$2:$B$11))

Where A2:A11 hold the total scores, and B2:B11 hold the dropped scores.
 
Upvote 0
Alright...I'll try to get the example formatted here...

But I've tried that formula before, found it elsewhere (edited to suit my ranges), but there are a few problems:

1) I don't need the rank itself, I need to keep the score.

2) It doesn't take into account current higher scores that aren't duplicated.

3) It doesn't go in order how I need it (1,2,3...), instead it goes 4,5,6,7,8,10,9,3,2,1

Here's the formula edited for my ranges (round 1 scores): =RANK(Sheet1!C$9,Sheet1!$C$9:$L$9)+SUMPRODUCT(--(Sheet1!$C$9:$L$9 = Sheet1!C$9),--(Sheet1!C$6 > Sheet1!$C$6:$L$6))

That's taking rows 9 and 6 from sheet 1, where row 9 is the score, and row 6 is the dropped value.

Here's are my sheets:

Sheet 1:

<table border="0" cellpadding="0" cellspacing="0" width="836"><col style="width: 65pt;" width="86"> <col style="width: 56pt;" span="10" width="75"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 65pt;" height="17" width="86">Name</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 1</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 2</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 3</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 4</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 5</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 6</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 7</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 8</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 9</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Name 10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">4</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">4</td> <td class="xl72" style="border-left: medium none;">4</td> <td class="xl72" style="border-left: medium none;">4</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">8.9</td> <td class="xl72" style="border-left: medium none;">9.4</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">Time</td> <td class="xl73" style="border-left: medium none;">5TH</td> <td class="xl73" style="border-left: medium none;">4TH</td> <td class="xl73" style="border-left: medium none;">(7TH)</td> <td class="xl73" style="border-left: medium none;">6TH</td> <td class="xl73" style="border-left: medium none;">(10TH)</td> <td class="xl73" style="border-left: medium none;">(8TH)</td> <td class="xl73" style="border-left: medium none;">(9TH)</td> <td class="xl73" style="border-left: medium none;">3RD</td> <td class="xl73" style="border-left: medium none;">2ND</td> <td class="xl73" style="border-left: medium none;">1ST</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">Penalty</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Round 1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">22</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">22</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">24</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">26</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">27</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl76" style="border-left: medium none;">6</td> <td class="xl76" style="border-left: medium none;">4</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">5</td> <td class="xl77">
</td> <td class="xl76">
</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">6</td> <td class="xl76" style="border-left: medium none;">5</td> <td class="xl76" style="border-left: medium none;">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">5</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl77">
</td> <td class="xl72">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl77">
</td> <td class="xl72">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl77">
</td> <td class="xl72">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">Time</td> <td class="xl73" style="border-left: medium none;">2ND</td> <td class="xl73" style="border-left: medium none;">(5TH)</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl73" style="border-left: medium none;">4TH</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl73" style="border-left: medium none;">1ST</td> <td class="xl73" style="border-left: medium none;">3RD</td> <td class="xl73" style="border-left: medium none;">(6TH)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">Penalty</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Round 2</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">24</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">24</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">Total</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">46</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">40</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">42</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">48</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">47</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">42</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl76" style="border-left: medium none;">7</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">7</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">
</td> <td class="xl76" style="border-left: medium none;">5</td> <td class="xl76" style="border-left: medium none;">6</td> <td class="xl76" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">8.5</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">6</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">9.5</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">7</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9.2</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">9.2</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">8</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl72" style="border-left: medium none;">9.9</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">10</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">
</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">9</td> <td class="xl72" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">Time</td> <td class="xl73" style="border-left: medium none;">(2ND)</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl73" style="border-left: medium none;">1ST</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl78" style="border-left: medium none;">
</td> <td class="xl73" style="border-left: medium none;">(4TH)</td> <td class="xl73" style="border-left: medium none;">(3RD)</td> <td class="xl78" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">Penalty</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Round 3</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">26.7</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">26.7</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">24</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">Total</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">72.7</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">40</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">68.7</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">69</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">71</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">42</td> </tr> </tbody></table>
Sheet 2:

<table border="0" cellpadding="0" cellspacing="0" width="752"><col style="width: 48pt;" width="64"> <col style="width: 53pt;" span="8" width="70"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 48pt;" height="17" width="64">R1 Scores</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">27</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">26</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">24</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">22</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">22</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">21</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">21</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">18</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">18</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Names</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 3</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 6</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 7</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl70">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">R2 Clean</td> <td class="xl67" style="border-left: medium none;">24</td> <td class="xl67" style="border-left: medium none;">24</td> <td class="xl67" style="border-left: medium none;">21</td> <td class="xl67" style="border-left: medium none;">21</td> <td class="xl67" style="border-left: medium none;">18</td> <td class="xl67" style="border-left: medium none;">15</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Names</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl70">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">R2 Comb.</td> <td class="xl67" style="border-left: medium none;">48</td> <td class="xl67" style="border-left: medium none;">47</td> <td class="xl67" style="border-left: medium none;">46</td> <td class="xl67" style="border-left: medium none;">42</td> <td class="xl67" style="border-left: medium none;">42</td> <td class="xl67" style="border-left: medium none;">40</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Names</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl70">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">R3 Clean</td> <td class="xl67" style="border-left: medium none;">26.7</td> <td class="xl67" style="border-left: medium none;">26.7</td> <td class="xl67" style="border-left: medium none;">24</td> <td class="xl67" style="border-left: medium none;">21</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Names</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl71">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl70">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">R3 Comb.</td> <td class="xl67" style="border-left: medium none;">72.7</td> <td class="xl67" style="border-left: medium none;">71</td> <td class="xl67" style="border-left: medium none;">69</td> <td class="xl67" style="border-left: medium none;">68.7</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">Names</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Name 4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

Ok, so it seems to have worked...hopefully this clears things up. Thanks!
 
Upvote 0
Also, here is the macro to determine highest to lowest score so the time can be pasted from 1st to last between rounds 2 and 3. In rows 28, 29, 30, 31 and 32 are formulas I have to currently keep duplicates ranked (score / 100000 to make it 21.7000001, 21.7000002 etc.). However, I need to determine highest to lowest duplicates based on highest dropped score, not the division formula I currently have. Thanks!

Code: 
If WorksheetFunction.Large(Range("C28:L28"), 1) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 1) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 2) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 3) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 4) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 5) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("C28") _
And IsEmpty(Range("C15")) Then
Range("C15").Select
ActiveSheet.Paste
Range("C10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("D28") _
And IsEmpty(Range("D15")) Then
Range("D15").Select
ActiveSheet.Paste
Range("D10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("E28") _
And IsEmpty(Range("E15")) Then
Range("E15").Select
ActiveSheet.Paste
Range("E10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("F28") _
And IsEmpty(Range("F15")) Then
Range("F15").Select
ActiveSheet.Paste
Range("F10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("G28") _
And IsEmpty(Range("G15")) Then
Range("G15").Select
ActiveSheet.Paste
Range("G10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("H28") _
And IsEmpty(Range("H15")) Then
Range("H15").Select
ActiveSheet.Paste
Range("H10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("I28") _
And IsEmpty(Range("I15")) Then
Range("I15").Select
ActiveSheet.Paste
Range("I10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("J28") _
And IsEmpty(Range("J15")) Then
Range("J15").Select
ActiveSheet.Paste
Range("J10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("K28") _
And IsEmpty(Range("K15")) Then
Range("K15").Select
ActiveSheet.Paste
Range("K10").Select
ElseIf WorksheetFunction.Large(Range("C28:L28"), 6) = Range("L28") _
And IsEmpty(Range("L15")) Then
Range("L15").Select
ActiveSheet.Paste
Range("L10").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("C29") _
And IsEmpty(Range("C24")) Then
Range("C24").Select
ActiveSheet.Paste
Range("C19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("D29") _
And IsEmpty(Range("D24")) Then
Range("D24").Select
ActiveSheet.Paste
Range("D19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("E29") _
And IsEmpty(Range("E24")) Then
Range("E24").Select
ActiveSheet.Paste
Range("E19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("F29") _
And IsEmpty(Range("F24")) Then
Range("F24").Select
ActiveSheet.Paste
Range("F19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("G29") _
And IsEmpty(Range("G24")) Then
Range("G24").Select
ActiveSheet.Paste
Range("G19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("H29") _
And IsEmpty(Range("H24")) Then
Range("H24").Select
ActiveSheet.Paste
Range("H19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("I29") _
And IsEmpty(Range("I24")) Then
Range("I24").Select
ActiveSheet.Paste
Range("I19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("J29") _
And IsEmpty(Range("J24")) Then
Range("J24").Select
ActiveSheet.Paste
Range("J19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("K29") _
And IsEmpty(Range("K24")) Then
Range("K24").Select
ActiveSheet.Paste
Range("K19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 1) = Range("L29") _
And IsEmpty(Range("L24")) Then
Range("L24").Select
ActiveSheet.Paste
Range("L19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("C29") _
And IsEmpty(Range("C24")) Then
Range("C24").Select
ActiveSheet.Paste
Range("C19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("D29") _
And IsEmpty(Range("D24")) Then
Range("D24").Select
ActiveSheet.Paste
Range("D19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("E29") _
And IsEmpty(Range("E24")) Then
Range("E24").Select
ActiveSheet.Paste
Range("E19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("F29") _
And IsEmpty(Range("F24")) Then
Range("F24").Select
ActiveSheet.Paste
Range("F19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("G29") _
And IsEmpty(Range("G24")) Then
Range("G24").Select
ActiveSheet.Paste
Range("G19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("H29") _
And IsEmpty(Range("H24")) Then
Range("H24").Select
ActiveSheet.Paste
Range("H19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("I29") _
And IsEmpty(Range("I24")) Then
Range("I24").Select
ActiveSheet.Paste
Range("I19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("J29") _
And IsEmpty(Range("J24")) Then
Range("J24").Select
ActiveSheet.Paste
Range("J19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("K29") _
And IsEmpty(Range("K24")) Then
Range("K24").Select
ActiveSheet.Paste
Range("K19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 2) = Range("L29") _
And IsEmpty(Range("L24")) Then
Range("L24").Select
ActiveSheet.Paste
Range("L19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("C29") _
And IsEmpty(Range("C24")) Then
Range("C24").Select
ActiveSheet.Paste
Range("C19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("D29") _
And IsEmpty(Range("D24")) Then
Range("D24").Select
ActiveSheet.Paste
Range("D19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("E29") _
And IsEmpty(Range("E24")) Then
Range("E24").Select
ActiveSheet.Paste
Range("E19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("F29") _
And IsEmpty(Range("F24")) Then
Range("F24").Select
ActiveSheet.Paste
Range("F19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("G29") _
And IsEmpty(Range("G24")) Then
Range("G24").Select
ActiveSheet.Paste
Range("G19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("H29") _
And IsEmpty(Range("H24")) Then
Range("H24").Select
ActiveSheet.Paste
Range("H19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("I29") _
And IsEmpty(Range("I24")) Then
Range("I24").Select
ActiveSheet.Paste
Range("I19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("J29") _
And IsEmpty(Range("J24")) Then
Range("J24").Select
ActiveSheet.Paste
Range("J19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("K29") _
And IsEmpty(Range("K24")) Then
Range("K24").Select
ActiveSheet.Paste
Range("K19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 3) = Range("L29") _
And IsEmpty(Range("L24")) Then
Range("L24").Select
ActiveSheet.Paste
Range("L19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("C29") _
And IsEmpty(Range("C24")) Then
Range("C24").Select
ActiveSheet.Paste
Range("C19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("D29") _
And IsEmpty(Range("D24")) Then
Range("D24").Select
ActiveSheet.Paste
Range("D19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("E29") _
And IsEmpty(Range("E24")) Then
Range("E24").Select
ActiveSheet.Paste
Range("E19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("F29") _
And IsEmpty(Range("F24")) Then
Range("F24").Select
ActiveSheet.Paste
Range("F19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("G29") _
And IsEmpty(Range("G24")) Then
Range("G24").Select
ActiveSheet.Paste
Range("G19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("H29") _
And IsEmpty(Range("H24")) Then
Range("H24").Select
ActiveSheet.Paste
Range("H19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("I29") _
And IsEmpty(Range("I24")) Then
Range("I24").Select
ActiveSheet.Paste
Range("I19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("J29") _
And IsEmpty(Range("J24")) Then
Range("J24").Select
ActiveSheet.Paste
Range("J19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("K29") _
And IsEmpty(Range("K24")) Then
Range("K24").Select
ActiveSheet.Paste
Range("K19").Select
ElseIf WorksheetFunction.Large(Range("C29:L29"), 4) = Range("L29") _
And IsEmpty(Range("L24")) Then
Range("L24").Select
ActiveSheet.Paste
Range("L19").Select
 
Upvote 0
Ok, so am I getting this right now? Sheet 2 in the sample above you created manually, and you want something that will do it for you?

For a starter,

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 93px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">R1 Scores</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">27</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">26</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">24</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">22</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">22</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Names</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 10</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 9</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name 5</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B31</TD><TD>=LARGE($B$9:$K$9,COLUMN(A:A))</TD></TR><TR><TD>B32</TD><TD>{=INDEX($B$1:$K$1,SUMPRODUCT(--($B$9:$K$9=B31),--($B$6:$K$6=(LARGE(IF($B$9:$K$9=B31,$B$6:$K$6),COUNTIF($B$31:B$31,B31)))),COLUMN($A:$J)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Drag / fill both formula to column K
 
Upvote 0
Dude! That is perfect! Exactly what I needed! (With a bit of editing of course.) Thank you so much! Thanks for the quick response! Now I just have to finish the document and edit the formulas and macros accordingly. You, jasonb75, are a savior!
 
Upvote 0
To my mind, the use of COLUMN() in B31 is risky. If you subsequently insert a new column at the left for some reason, the formulas will return unexpected results.

That would easily be overcome by using this in B31 instead
=LARGE($B$9:$K$9,COLUMNS($B:B))

If you wanted to consider an option that avoids array formulas, but does use an extra helper row (could be hidden), then I think this also does what you want. All formulas copied across.


Excel Workbook
ABCDEFGHIJK
1NameName 1Name 2Name 3Name 4Name 5Name 6Name 7Name 8Name 9Name 10
24555444678
36666555788
47777666899
599887779910
69108.99.48109101010
7Time5TH4TH(7TH)6TH(10TH)(8TH)(9TH)3RD2ND1ST
8Penalty0000000000
9Round 122222121181818242627
30
3127262422222121181818
32Name 10Name 9Name 8Name 2Name 1Name 4Name 3Name 6Name 7Name 5
33
344.1111114.16.1123595516.1063829798.1258.18.111111113.12.11.1
Sheet1
 
Upvote 0
Hello Peter,

Thanks for the info. I will keep that in mind. However, with the sheets I use, they are static, so if anything were to be added, it would be on the right, not the left. Though you do have some good points. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
349
Fluff
Fluff
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
504
StephenCrump
S
mrcsbenson
  • Locked
  • Question Question
Updating Ranking Column
Replies
1
Views
339
Fluff
Fluff
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
133
dreid1011
dreid1011
JTL9161
  • Solved
Calculating Golf Handicap
2
Replies
10
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top