+ Post New Thread
Results 1 to 5 of 5
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 ...
  1. #1
    mdench's Avatar
    Join Date
    Oct 2011
    Location
    Billingshurst
    Posts
    810
    Thank Post
    40
    Thanked 104 Times in 94 Posts
    Rep Power
    39

    Question 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:

    1.JPG

    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.

    2.JPG

    Hopefully that has made sense for a post at 2am

    Cheers

    M
    Attached Images Attached Images
    • File Type: jpg 1.JPG (77.1 KB, 11 views)

  2. #2
    clareq's Avatar
    Join Date
    Dec 2005
    Location
    Doncaster
    Posts
    744
    Thank Post
    56
    Thanked 190 Times in 126 Posts
    Rep Power
    101
    Would vlookup do what you require?

  3. #3
    mdench's Avatar
    Join Date
    Oct 2011
    Location
    Billingshurst
    Posts
    810
    Thank Post
    40
    Thanked 104 Times in 94 Posts
    Rep Power
    39
    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?

  4. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    I think you will need some VBA script to do what you want...

  5. #5


    Join Date
    May 2009
    Posts
    3,284
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    341
    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).

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 12
    Last Post: 31st March 2012, 04:06 PM
  2. [SIMS] Creating photolists of students based on assessment data in SIMs
    By dveldtman in forum MIS Systems
    Replies: 3
    Last Post: 14th September 2011, 07:48 AM
  3. [MS Office - 2003] Help with Excel Macro - Send email based on cell value.
    By Svarcy in forum Office Software
    Replies: 1
    Last Post: 14th June 2011, 08:02 AM
  4. [MS Office - 2010] Excel - Office 2010 - Not updating data totals on some PCs
    By DLW13 in forum Office Software
    Replies: 0
    Last Post: 10th February 2011, 09:37 AM
  5. File blocking based on a hash value
    By phodge in forum Windows
    Replies: 1
    Last Post: 30th March 2010, 03:11 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •