+ 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
    702
    Thank Post
    30
    Thanked 94 Times in 86 Posts
    Rep Power
    30

    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
    593
    Thank Post
    46
    Thanked 166 Times in 104 Posts
    Rep Power
    79
    Would vlookup do what you require?

  3. #3
    mdench's Avatar
    Join Date
    Oct 2011
    Location
    Billingshurst
    Posts
    702
    Thank Post
    30
    Thanked 94 Times in 86 Posts
    Rep Power
    30
    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,394
    Thank Post
    108
    Thanked 266 Times in 197 Posts
    Rep Power
    168
    I think you will need some VBA script to do what you want...

  5. #5


    Join Date
    May 2009
    Posts
    2,488
    Thank Post
    198
    Thanked 629 Times in 481 Posts
    Rep Power
    227
    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
  •