Results 1 to 9 of 9

Thread: VLOOKUP Challenge

  1. #1

    VLOOKUP Challenge



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm fairly green to Excel and this task is proving very difficult (or impossible) for me, can you help?

    So here's what I'm trying to do:
    - Sheet 1 of my Workbook: Columns 1 - 10 contain item information extracted from a report, Columns 11 & 12 have date information entered manually.
    > as time passes some items are received (thus falling off of the report) and some items are added (thus adding onto the report), so the report that the item information is extracted from is constantly changing.

    - Sheet 2 of my Workbook is the most up-to-date version of the report: Columns 1 - 10 contain the same item information as Sheet 1, and Columns 11 & 12 still need date information.
    > So, how can I get the manually entered date information from Sheet 1 to follow the item information to Sheet 2? Some items will have fallen off, some items will remain (these are the ones needing the dates to transfer...using VLOOKUP, and maybe IFERROR and/or MATCH???), and some items will be new (needing new date information to be manually entered) - I'm trying to only enter the date information once and avoid having to re-enter all of the date information every time I update the report (which is four times per day).

    - One aspect of the challenge is that the item information on the spreadsheet is filtered from the report so what would usually be Row 2 might read Row 547 on Sheet 1, and the next time the report is updated the same item might still be where Row 2 normally is but now it might read Row 532 on Sheet 2...the Row that's 20 rows down from the top might read Row 1237 on Sheet 1, and the next time the report is updated that item might now be 29 rows down from the top and now read Row 1286 - so the Rows are always jumping around but I need the date information to stick with the item information, not the Row #.
    > Every time I update the report I cut the spreadsheet from Sheet 2 and paste it over Sheet 1, next I paste the updated report information onto Sheet 2, then (if I can figure out how to get all of this to work) I'll just have a few blanks in Columns 11 & 12 to fill in and the report is complete and up-to-date.

    See attachment for a sample.

    Any help would be sooo much appreciated!

    Thank you!
    Attached Files Attached Files
    Last edited by love2clym; 2014-04-30 at 03:52 AM. Reason: add to text

  2. #2
    Not clear about what you need as a results, Is it you need to combine both data in Sheet 1 and Sheet 2?

  3. #3

    Correction to attachment

    Sorry, the information on attachment was probably confusing - it is now corrected. What I've done is manually entered the date information for the new items on Sheet 2, and the 'VLOOKUP Challenge' would be to get the date information from Sheet 1 to automatically transfer, or auto populate, to Sheet 2 (for items that are on both Sheet 1 and Sheet 2), and this would fill in all of the blanks, completing the report.

    Sorry for the confusion. Thanks again for you help!
    Attached Files Attached Files
    Last edited by love2clym; 2014-04-30 at 12:12 PM. Reason: Correction to attachment

  4. #4
    Sorry, I fixed the attachment so hopefully that helps clear up the confusion - I need the date information from Sheet 1 to auto populate in Sheet 2 for the items that remain on the report. Sheet 2 already has the date information manually entered for the new items on the report. So, basically I'm trying to get all of the blanks on Sheet 2 to fill in automatically.

    Thank you!

  5. #5
    Perhaps something like this? please see the file
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Would it be just the part no. that would ensure that the correct row is identified? If not, what combination of columns would you need to check to guarantee that the dates etc. are being fetched from the correct row?

  7. #7

    Something like this? -Yes!

    Quote Originally Posted by Rizky View Post
    Perhaps something like this? please see the file
    Yes! Yes, like that! Is there a formula that can perform such a task?

  8. #8
    Quote Originally Posted by p45cal View Post
    Would it be just the part no. that would ensure that the correct row is identified? If not, what combination of columns would you need to check to guarantee that the dates etc. are being fetched from the correct row?
    Unfortunately the part number alone wouldn't be specific enough, as sometimes there is no part number generated from the report, and sometimes there are two or more of the same part numbers on order for different jobs. So, I would say at least the Vendor Name combined with the RO #. An exact match of Columns 1 - 9 would be great for maximum accuracy but if it's not possible then it is what is.

    Any help is very much appreciated. Thank you so much!

  9. #9
    Quote Originally Posted by love2clym View Post
    Yes! Yes, like that! Is there a formula that can perform such a task?
    Yes the highlited cells they have those formula...

Posting Permissions

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