+ Reply to Thread
Results 1 to 10 of 10

Match and extract data on different sheets

  1. 11-01-2017, 11:32 PM #1
    navikn
    navikn is offline
    Registered User
    Join Date
    11-01-2017
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    4

    Match and extract data on different sheets

    Hello dear,

    I have two different sheets in where I want to match column A(date) and column B(Vehicle Type) on both sheets and extract driver name from sheet 1 to sheet 2.
    How can I get excel to automatically populate driver's name on sheet 2 where A(date) and B (Vehicle) are matched on both sheets.
    Any way to solve this by formula, macro, or VBA.....?

    Any help will be appreciated.

    Thank you,




    Sheet 1
    Date Vehicle Driver
    1-Sep-17 B 1180 MIKE
    1-Sep-17 B 1180 MIKE
    1-Sep-17 B 1180 MIKE
    1-Sep-17 B 1180 MIKE
    1-Sep-17 B 1182 JOHN
    1-Sep-17 B 1182 JOHN
    1-Sep-17 B 1182 JOHN
    1-Sep-17 B 1190 PAUL
    1-Sep-17 B 1190 PAUL
    1-Sep-17 B 1190 PAUL
    1-Sep-17 B 1190 PAUL
    1-Sep-17 B 1190 PAUL
    1-Sep-17 B 1195 CEASER
    1-Sep-17 B 1195 CEASER
    1-Sep-17 B 1195 CEASER
    1-Sep-17 B 1195 CEASER


    Sheet 2
    Date Vehicle ID Driver Name
    1-Sep-17 B 1180 ???
    1-Sep-17 B 1181 ???
    1-Sep-17 B 1182 ???
    1-Sep-17 B 1190 ???
    1-Sep-17 B 1195 ???
    Reply With QuoteRegister To Reply
  2. 11-01-2017, 11:53 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Match and extract data on different sheets

    Hi navikn, welcome! Try this INDEX/MATCH lookup formula in Sheet2!C2 and copied down. (Data on both sheets starts in A2): 
    Please Login or Register  to view this content.
    Sheet2 listed one vehicle ID not found on Sheet1, causing an error. The IFERROR statement returns "-" in such cases.
    Attached Files Attached Files
    Last edited by leelnich; 11-02-2017 at 12:02 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 11-02-2017, 09:51 AM #3
    navikn
    navikn is offline
    Registered User
    Join Date
    11-01-2017
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Match and extract data on different sheets

    Thank you for quick reply

    It is working for Oct 1st but doesn't give return value for Oct 2nd and beyond.
    This may be simple but I am unable to get it.

    Please update.

    Thank you,

    navikn
    Reply With QuoteRegister To Reply
  4. 11-02-2017, 10:50 AM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Match and extract data on different sheets

    It's working OK with an expanded list in my copy. The problem is almost certainly differences in the way Dates or IDs are entered on the 2 sheets. Could you upload a sample of your workbook so we can take a look?

    Please remove any sensitive or extraneous info.
    Try to preserve the original layout so our solutions fit your workbook.
    Provide “realistic” data. Include any variations the code or formula must address.


    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now click Browse, find your file, then click Upload. Simple!

    Book1v2.xlsx
    Last edited by leelnich; 11-02-2017 at 11:01 AM.
    Reply With QuoteRegister To Reply
  5. 11-02-2017, 11:34 AM #5
    queuesef
    queuesef is offline
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Match and extract data on different sheets

    It appears you need a list of Drivers and their ID. You may need to separate the Vehicle field into 2 fields (the 'B' and the 4 digit number).
    You could also use vLookup on this then once you have a 'Table' of drivers (Ex. 1180 = Mike; 1182 = John; 1190 = Paul etc). see attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 11-02-2017, 12:26 PM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Match and extract data on different sheets

    Quote Originally Posted by queuesef View Post
    It appears you need a list of Drivers and their ID...
    I think the OP wanted to check dates because more than one driver may use each vehicle.
    Quote Originally Posted by navikn View Post
    How can I get excel to automatically populate driver's name on sheet 2 where A(date) and B (Vehicle) are matched on both sheets.
    Last edited by leelnich; 11-02-2017 at 12:28 PM.
    Reply With QuoteRegister To Reply
  7. 11-02-2017, 10:30 PM #7
    navikn
    navikn is offline
    Registered User
    Join Date
    11-01-2017
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Match and extract data on different sheets

    Hello Dear,

    Please find the attached workbook as requested
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-02-2017, 11:09 PM #8
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Match and extract data on different sheets

    You didn't extend the ranges used in the formula downward to allow for the extra data. If you're interested, I can help you set up Dynamic Named Ranges that automatically adjust to extra data. In any event, paste this in D2 and copy down: 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-03-2017, 12:01 AM #9
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Match and extract data on different sheets

    Here's a version employing Dynamic Named Ranges, which use formulas to find and include the last row of data. Again, in D2 and down: 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leelnich; 11-03-2017 at 12:08 AM.
    Reply With QuoteRegister To Reply
  10. 11-06-2017, 11:27 PM #10
    navikn
    navikn is offline
    Registered User
    Join Date
    11-01-2017
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Match and extract data on different sheets

    Hello
    Definitely interested in Dynamic Name Ranges, and I see you have already send the new sheet.

    Also if possible, please explain as how the formula is working.
    Many Thanks,
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract data from third column where data in two other columns match
    By redimp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-16-2014, 10:01 AM
  2. match data 2 sheets. if match, then copy data from one sheet to another
    By flunzy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-09-2013, 06:38 AM
  3. How to extract data from multiple sheets
    By somnath6309 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2013, 10:18 AM
  4. [SOLVED] Two Excel sheets trying to match text data and return the match from the second column!
    By bankcott in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-02-2013, 02:17 PM
  5. [SOLVED] Find a match between 2 sheets and extract a specified value
    By Radical_Magic63 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-17-2012, 07:16 PM
  6. Extract data from multiple sheets with # match
    By Jim15 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-07-2008, 07:16 PM
  7. Match and Extract Data Automatically
    By bs2713 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2008, 02:00 AM
  8. [SOLVED] How to extract the same data from different sheets
    By [email protected] in forum Excel General
    Replies: 3
    Last Post: 12-02-2005, 08:10 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1