How do you do....it? Thread, Excel - Copy data based on row value in Technical; Morning,
Been asked by my little brother to give a hand out with their green power car team and creating ...
29th April 2012, 03:14 AM #1
Excel - Copy data based on row value
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
29th April 2012, 03:27 PM #2
Would vlookup do what you require?
29th April 2012, 04:11 PM #3
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, 04:32 PM #4
I think you will need some VBA script to do what you want...
29th April 2012, 06:39 PM #5
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).
By HodgeHi in forum Coding
Last Post: 31st March 2012, 05:06 PM
By dveldtman in forum MIS Systems
Last Post: 14th September 2011, 08:48 AM
By Svarcy in forum Office Software
Last Post: 14th June 2011, 09:02 AM
By DLW13 in forum Office Software
Last Post: 10th February 2011, 10:37 AM
By phodge in forum Windows
Last Post: 30th March 2010, 04:11 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)