Excel - Copy data based on row value

[mdench]

Morning,

 

Been asked by my little brother to give a hand out with their green power car team and creating a spreadsheet that enables them to input driver lap times and it works out average speed etc etc. Which currently looks like the image below:

 

 

Because they have multiple drivers per race they want to be able to split this large table up and be able to quickly view smaller tables for the individual drivers. So my question is can i pull the data out of this table by referencing the Driver ID number (the first column) and have it copy it onto a smaller tables for each driver? Mainly looking at having it pull out the lap number, lap time and pit time such as the tables shown below and if i can how do i do it? Been racking my brain trying to think of a function that will do it.

 

 

Hopefully that has made sense for a post at 2am

 

Cheers

 

M

[clareq]

Would vlookup do what you require?

[mdench]

can you set up VLOOKUP to copy over all rows that have a Driver ID of 1 and then do the same for ID 2, ID 3 etc into their respective smaller tables?

[CESIL]

I think you will need some VBA script to do what you want...

[pcstru]

You can do it with vlookup with a bit of shuftying (good Scottish word) around. You need two new columns DriverLap and DLIdx. DLIdx should be the first column (insert it as col A). Driverlap can go anywhere.

 

Into DriverLap we want to calculate a unique lap number for the driver. We simply need to count the instances of DriverID but we need to make the formula look at all the cells above but none below. On YOUR sheet (without the extra column), we can simply insert a new column somewhere and enter the forumla =COUNTIF(A$2:A2,A2) and copy the forumula down the column.

 

Now insert a new column A and title it DLIdx. What you want here is a unique value of DriverID and DriverLap so assuming DriverID is now column B and our DriverLap column of the previous step is column I, the forumula is simply = B2&I2.

 

Now you can create a new sheet and in A1 put the driver ID. From A3 down count 1,2,3 etc. In B3 down = $A$1&A3 (so you get 11, 12, 13 etc). You can now use VLOOKUP into the first sheet based on the contents of B3 down into the first sheet and pull the information with no gaps etc (i.e. lookup the laptime =VLOOKUP(B5,Sheet1!$A$2:$I$28,4,FALSE).