Excel - Copy data based on row value

• 29th April 2012, 02:14 AM
mdench
Excel - Copy data based on row value
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:

Attachment 13793

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.

Attachment 13794

Hopefully that has made sense for a post at 2am

Cheers

M
• 29th April 2012, 02:27 PM
clareq
Would vlookup do what you require?
• 29th April 2012, 03:11 PM
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?
• 29th April 2012, 03:32 PM
CESIL
I think you will need some VBA script to do what you want...
• 29th April 2012, 05:39 PM
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).