+ Reply to Thread
Results 1 to 6 of 6

Lookup values from two different worksheets

  1. #1
    Registered User
    Join Date
    03-26-2020
    Location
    Karachi, Pakistan
    MS-Off Ver
    2016
    Posts
    5

    Lookup values from two different worksheets

    I have the following two sources of data :
    1- System
    2- Manual Records

    The system records tell me the time travel, fuel used and refuel for vehicles movement day-wise whereas the Manual records tell me the start and end points and dates for these vehicles movements. The system records have the data for productive trips (sales) and non-productive trips (all except sales e.g. maintenance , servicing , to/ from parking etc.).

    The problem that I am having is that I want to combine these two data streams i.e. System and Manual records to tap-out some meaningful information about complete trip movements from one location to another. Wondering how I can tag/pair/lookup the productive trips start and end point (from manual records) into the system records so that I may know how much fuel the vehicle consumed per trip (according to location for complete trip). i tried combining the date & vehicle # (concatenate) to create the a unique-ID and look-up the start/end points but it only gives me the lookup values for the two dates i.e. start date and end date and returns blank for all the date in-between e.g. If i look up the unique-ID for a trip from Location 1 to destination 1 duration of which was 4 days starting from 1st July-4th July, I get the lookup values for 1st and 4th July only.

    Can some please suggest me the way around here, Data file attached for reference .. Thanks in advance
    Attached Files Attached Files
    Last edited by Abdul Moix; 08-06-2020 at 08:11 AM. Reason: Want to attach a new data file as inquired by one of the members

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: Lookup values from two different worksheets

    Welcome to the forum.

    You will need to mock up 10-15 rows of RESULTS data, pleas, so that we can see what you are aiming to achieve.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-26-2020
    Location
    Karachi, Pakistan
    MS-Off Ver
    2016
    Posts
    5

    Re: Lookup values from two different worksheets

    AliGW :: Thank you for your prompt response .. I have attached an updated file with the required results (in "Results" work sheet) in my original message above. Please take a look ..

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Lookup values from two different worksheets

    I assume that Vehicle # is the common key between System and ManualRecords.

    However, I see multiple records in both System and ManualRecords with the same Vehicle #. How do we know which record matches with which?
    I see that the vehicle #'s match row by row until row 35. However, this "rule" breaks down though after row 35.

  5. #5
    Registered User
    Join Date
    03-26-2020
    Location
    Karachi, Pakistan
    MS-Off Ver
    2016
    Posts
    5

    Re: Lookup values from two different worksheets

    Yes ! Vehicles are the common key here.
    as for the vehicle # .. the sequence could be different (I just made up this dummy data).

    Just to make it clear further, manual records only possess information for productive trips and we need to incorporate them with system records (which has other data related to all trips i.e. productive & non-productive) with respect to dates.

    Hope it helps ..

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Lookup values from two different worksheets

    I'm still having a hard time understanding what is required. Perhaps if we focus on one vehicle in particular it will help. I have chosen vehicle 1409 as that is the first entry in your expected results.

    I manually merged your manual and system worksheets and then sorted the merged sheet by vehicle # and then by date. The following is a snapshot of this merged sheet for vehicle #1409.

    AbdulMoix.png

    Your expected results:

    AbdulMoix1.png

    Your expected results say the trip end date is 3-Jul-20 yet there is no record with this date in any of the 1409 data. Are you wanting a summary of ALL data 1-Jul-20 through 22-Jul-20?

    Your expected results say the total fuel used is 500 liters, yet the sum of all 1409 fuel used is just 99 liters. Same with distance covered.

    It would help, I think, if you would provide a sample for just vehicle 1409 and its seven associated records in the system and manual worksheets that are fully consistent with each other and with your expected results for vehicle 1409.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup / Indirect function to lookup information from various tabs/worksheets
    By rajeshpansara in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-20-2019, 10:29 PM
  2. [SOLVED] Lookup Date minus 1 day and return Values from the Matching Headings in 2 Worksheets
    By hammer2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2015, 09:07 AM
  3. Replies: 2
    Last Post: 05-06-2013, 08:53 AM
  4. [SOLVED] Need Two-Way Lookup Using 2 Lookup Criteria and Two Worksheets
    By CM_Marsh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2012, 05:10 AM
  5. Lookup Values, 2 Worksheets, one similar column
    By ladron in forum Excel General
    Replies: 5
    Last Post: 09-20-2012, 04:19 AM
  6. [SOLVED] Lookup values across range of worksheets
    By lucasreece in forum Excel General
    Replies: 3
    Last Post: 03-30-2012, 05:13 PM
  7. Lookup values from several different worksheets
    By gunerstahl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2011, 06:37 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1