Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Create a Vlookup formula

Options
  • 26-10-2016 2:35pm
    #1
    Ben_Nevis
    Registered Users Posts: 26 ✭


    Can anybody please help me with a Excel Vlookup formula?

    If I receive two spreadsheets weekly, each time being updated with new information (but retaining everything previous), how do I create a Vlookup formula to pick out what is different on the new sheet that wasn't there on the previous?

    Currently there are 1500-2000 rows of data which increases bit by bit with each sheet, and I have about 15 columns which separate the information (only the number of rows increases).

    I need the current sheet to look at the previous sheet and pick out what info is new/different on the current sheet and leave an imprint in the next column along that's free - doesn't matter what the imprint is, it can be anything so long as it shows this piece of data wasn't on the previous sheet.


Comments

  • Options
    #2
    Dravokivich
    Moderators, Category Moderators, Music Moderators, Politics Moderators, Society & Culture Moderators Posts: 22,360 CMod ✭✭✭✭Dravokivich


    Is there a column with a unique identifying reference, that won't be repeated in other rows? (Such as a ticket number / sale ID)

    If so, all you need to do is look that up, if it doesn't exist in the previous sheet, that is your new data.

    or...

    You can copy your previous data into the new worksheet, then do a remove duplicates...


  • Options
    #3
    Ben_Nevis
    Registered Users Posts: 26 ✭Ben_Nevis


    Thanks for your response.

    The remove duplicates won't work as some of the cells aren't exactly the same size as the rest.

    These sheets list invoices numbers, dates, company names, invoice amounts, amount paid off the invoice, amount outstanding - so one week the sheet could have €5 paid off an invoice, but the following week the same invoice could have the full balance paid off. I have to update my system with payments as and when they appear on the sheets.

    So I need Vlookup to look at everything and even if there's a difference of five cents, to highlight there is something that needs looking at.

    The sheets have all invoices that are outstanding, even ones that have zero payments on them.


  • Options
    #4
    cee_jay
    Registered Users Posts: 7,920 ✭✭✭cee_jay


    Is the invoice number field a unique identifier?


  • Options
    #5
    Ben_Nevis
    Registered Users Posts: 26 ✭Ben_Nevis


    Yes the invoice number is different however one sheet could have that invoice number partly paid and the next sheet could have it fully paid - I'd need it to highlight firstly on the previous sheet that it was partly paid so I can add that part payment to the system and then when the next sheet arrives it needs to highlight that it's paid again but this time all paid.

    There are two columns that show this by the way, one column is headed "part paid" and the other "fully paid"


Advertisement