#-# range broken down to individual occurences

**kevsvette** · 11-22-2009, 02:11 PM

I need help …

I have a spreadsheet with the lap leaders for some races & I need a formula to give me Col. G. Col. D, E & F are givens.

Basically I need to know what driver led a given lap of a given race (Col. G). Attached is my attempt. Something to consider is I need a formula that can be pasted down Column G as from track to track, from race to race the # of laps will not be the same. The number of drivers that led a given race will not be the same.

I have an array formula in cells of Column G for the # of laps in a given race (i.e. if a race was 100 laps it would go from G3-G100). I ran it down to 500 just to cover myself.

Column G: { =INDEX($E$3:$E$500, MATCH($F3,VALUE(LEFT($D$3:$D$500,FIND(" ",$D$3:$D$500)-1)),1))}

…it kinda works, but for some reason when it comes to lap 251 it gives me a #N/A error. It gives me the correct driver for lap 250 & 252 and all other laps but not lap 251. Also, a minor inconvenience is that I have to put a space after the first number & if at all possible I don’t want to have to do that.

I would love a macro where all I need to do is paste Column A, press a command button, & the macro does everything for me but that’s probably asking a lot. I know how to record a macro but don’t know anything about VB.

Column D & F have to be formatted as text. It has to be text format or when I cut Column B to Column D it changes some numbers to date (i.e. 1-2 becomes January 2nd).

Column B:
=RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))

Column C:
=LEFT(A3,(LEN(A3)-LEN((RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))))))-1)[/SIZE]

**Richard Buttrey** · 11-22-2009, 02:22 PM

Hi,

Can we start from the basics since there seems a lot of repetition and holding the lap ranges as say '1-7', '8-26' etc. only complicates any subsequent formulae.

Also I don't fully understand what the Lap Leaders column means. What happens if D Green also leads on a later lap than lap 7? or am I misunderstanding thi?

How do you enter your data. that's the first thing to consider

Rgds

**daddylonglegs** · 11-22-2009, 02:51 PM

You're getting #N/A because some of the numbers in the "Lap" column were text formatted. You can fix that by just selecting the cells with 1 and 2 and filling down the column to ensure that all are numbers.

Would it be easier to just extract the 1st lap number?

On the attached I used this formula in B3 copied down to do that

=LOOKUP(10000,MID(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&1234567890)),{1,2,3,4})+0)

Then to get the driver in C3

=TRIM(LEFT(A3,FIND(B3,A3)-1))

Now with the lap numbers in D3 down I used this formula in E3

=IF(D3>H$1,"",LOOKUP(D3,B$3:C$100))

H1 contains a formula which calculates the last lap, i.e.

=REPLACE(LOOKUP("zzz",A:A),1,FIND("-",LOOKUP("zzz",A:A)),"")+0

see attached

**daddylonglegs** · 11-22-2009, 02:57 PM

If you really want to extract the lap range, i.e. 1-7 rather than just 1 then change the B3 formula to this one

=REPLACE(A3,1,FIND(" ",A3,4),"")

and E3 to

=IF(D3>H$1,"",LOOKUP(D3,LEFT(B$3:B$100,FIND("-",B$3:B$100&"-")-1)+0,C$3:C$100))

**kevsvette** · 11-22-2009, 03:26 PM

Sorry guys about the long confusing post…

1) I get data from the web & paste it into Column A.
2) The formulas in columns B & C separates the Drivers Name from the Laps that he led. A driver can lead multiple times through out the course of a race. He could lead 1 lap or lead a bunch of laps in any given time during a race.
3) I then copy data from B & C & paste special as a value in column D & E. Then I have to add a space after the first number (for the formula in Column G to work)

The end result I’m looking for is Columns F & G which gives me the name of the driver that lead a particular lap.

Column D has to be formatted as Text or it will change anything that could possible be a date to a date when I CopyNPaste from B&C to D&E. If column D is formatted as Text, then for the formula to work (in col. G) Column F has to be text.

**daddylonglegs** · 11-22-2009, 03:46 PM

If you look at the attachment above I think that should do what you want......or the alternatives proposed in the following post.

I realised that the H1 formula wouldn't work if the last laps was a single one rather than a range so better to use this version in H1

=LOOKUP(10000,RIGHT(SUBSTITUTE(LOOKUP("zzz",A:A),"-","x"),{1,2,3,4})+0)

**kevsvette** · 11-22-2009, 04:01 PM

DaddyLongLegs, thanks for the reply but I don't think that helps.

I want to know what driver led lap 1, led lap 2, led lap 3 etc... for each lap of a race which is the result in Column G.

If "Driver Name" led lap One thru lap twelve, then Column G will have "Driver Name" in cells G3:G12. If I don't need to seperate the name from the laps thats great but I think its necessary. I input the laps. Its the name for a given lap that I need.

**daddylonglegs** · 11-22-2009, 04:43 PM

I believe that's what it does....except I've cut out two columns, so the lap numbers are in column D and the lead driver for each lap is in column E

**kevsvette** · 11-22-2009, 05:37 PM

DaddyLongLegs, I'm a moron. I’m man enough to admit it.

I looked at your spreadsheet again & tried a couple of races & it works perfectly. Your solution (not that I fully understand how it works) is brilliant. I see what you did with the lead changes just haven’t yet figured out the rest, but regardless I would have never thought about coming at it from that direction.

That is the reason why I love trying to solve these types of problems. Kudos to you again DaddyLongLegs!!!