Splitting and ranking data

[ForumHelp]

Hi Guys,

I'm pretty much a novice on excel so any help I can gain here is much appreciated. I'm trying to use excel to help me automate a system for calculating the starting positions of karts in a particular race format at an indoor kart track. I've managed to sort out a qualifying session and then the semi finals. However, I can't then arrange the grids for the finals.

I've attached a sheet to show you how far I've got so far. All I need to work out how to do is to organise the names of the drivers into the correct starting order for the final races. The two first place finishers from the heats will be first and second in the A final (first being the one with the fastest lap of the two drivers, 2nd being the slower), the two second place drivers from the heats will be 3rd and 4th in the A final (3rd being the driver with the faster of the two laps) and so on...

The only other tricky bit is that there are not always 16 people in the race. Sometimes there are less. The finals are then just half the drivers in each, if it's an odd number the A final will be the larger of the two races. (i.e. if there are 11 drivers there will be 6 in the A final and 5 in the B final).

I hope I've managed to explain the problem and that there is a simple solution to this problem.

Many thanks in advance.

Ben

[ChemistB]

It's not pretty but try this.
In the top grid
=IFERROR(IF(VLOOKUP(ROWS($A$1:A1),$I$3:$J$10,2,FALSE)<=VLOOKUP(ROWS($A$1:A1),$I$11:$J$18,2,FALSE),INDEX($H$3:$H$10,MATCH(ROWS($A$1:A1),$I$3:$I$10,0)),INDEX($H$11:$H$18,MATCH(ROWS($A$1:A1),$I$11:$I$18,0))),IF(COUNTIF($I$3:$I$18,ROWS($A$1:A1))=1,INDEX($H$3:$H$10,MATCH(ROWS($A$1:A1),$I$3:$I$10,0)),"")) copied down 8 rows
In the bottom grid
=IFERROR(IF(VLOOKUP(ROWS($A$1:A1),$I$3:$J$10,2,FALSE)>VLOOKUP(ROWS($A$1:A1), $I$11:$J$18,2,FALSE), INDEX($H$3:$H$10,MATCH(ROWS($A$1:A1),$I$3:$I$10,0)),INDEX($H$11:$H$18,MATCH(ROWS($A$1:A1),$I$11:$I$18,0))),"")
copied down 8 rows.
See attachment

[ForumHelp]

Hi ChemistB,

Thanks for the reply.

I'm not sure I explained the process of calculating the grid out correctly as the results gained are not correct. I've attached the sheet again but this time I've added a table below your sheet with the correct grids. I have inputted these manually so they are correct. It should also make it easier to understand exactly what i want the function to calculate. Sorry for any confusion.

Thanks again,

Ben

[AliGW]

How can Vicky have come first with the slowest lap in her semi?

[ForumHelp]

That's just her one off fastest lap. The others may have just done one fast lap or crashed etc.. The semi finals are held over 15 laps and the winner is the first over the line not the one who has the fastest lap.

The fastest laps are only used to differentiate between the winners of the two heats, the two second place driver etc..

That's what I don't think I made very clear in my first explanation, hence why I've manually entered the results to try and clarify.

Thanks.

[ChemistB]

Okay, I suggest you use a helper column (I used K) with this formula starting in K3
=IF(ISNUMBER(I3),I3*100+J3,"")
You can hide this column if you want.

Then in O3 copied down through Group A
=IF(INT((COUNTA($H$3:$H$18)+1)/2)>=ROWS($A$1:A1),INDEX($H$3:$H$18,MATCH(SMALL($K$3:$K$18,ROWS($A$1:A1)),($K$3:$K$18),0)),"")
In O11 copied down
=IF(INT((COUNTA($H$3:$H$18))/2)>=ROWS($A$1:A1),INDEX($H$3:$H$18,MATCH(SMALL($K$3:$K$18,ROWS($A$1:A9)),($K$3:$K$18),0)),"")
See attachment

[XOR LX]

Or, without a helper column, in N3 and copy down to N18, array formula**:

=IF(ROWS($1:1)>COUNTA($H$3:$H$18),"",INDEX($H$3:$H$18,MATCH(SMALL(IF($I$3:$I$18<>"",$I$3:$I$18+$J$3:$J$18/10^12),ROWS($1:1)),IF($I$3:$I$18<>"",$I$3:$I$18+$J$3:$J$18/10^12),0)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[ChemistB]

Nice XOR
Except I don't think it takes into account this bit;
... that there are not always 16 people in the race. Sometimes there are less. The finals are then just half the drivers in each, if it's an odd number the A final will be the larger of the two races. (i.e. if there are 11 drivers there will be 6 in the A final and 5 in the B final).

[ForumHelp]

ChemistB,

Thanks again. Much closer! Only one slight Issue now. When there are only 12 drivers it calculates two finals. One with 8 drivers and one with 4, this needs to be 6 in each.

[ForumHelp]

Chemist B,

When I enter details for 16 or 15 drivers the results are great. However, any less than this and I get an error message in the names column. Any ideas?

Thanks for your time, I appreciate your help greatly.

Ben

[ChemistB]

I needed to modify the formula in O11 down to
=IF(INT((COUNTA($H$3:$H$18))/2)>=ROWS($A$1:A1),INDEX($H$3:$H$18,MATCH(SMALL($K$3:$K$18,SUMPRODUCT(--(LEN($O$3:$O10)>0))+1),($K$3:$K$18),0)),"")
That should do it.

[ForumHelp]

ChemistB you are a superstar!

Thank you so much. If you are ever in the UK let me know and you can come karting for free!!

Just one last problem. When you remove drivers from the semi final i.e. delete so there are 12 not 16 the sheet works perfectly. However, If you remove them from the qualifying session (yes, the part that I did initially!) it doesn't work correctly and we end up with 8 and 4 drivers in the finals. Not 6 & 6.

Hopefully this will be the last time!

[ChemistB]

I think you'll need to upload an example where some things are not working.

[ForumHelp]

Hi again,

I've attached the file as requested with the errors in. As before I've also manualy enterred the correct grids in the table below.

Sorry I've not been explaining this too well.

Thanks again.

Ben