Compare columns + VLOOKUP?

[notski]

Hi!

Whilst I'm fairly comfortable with formulas I am incredibly new to VBA, so I apologize if this topic has been covered previously and would really appreciate a nudge in the right direction.

I have 2 workbooks, one which generates a report which I then copy into a second workbook. I use the second workbook throughout the week, adding notes etc. which need to be imported to workbook1 for the next report, to ensure notes are up to date.

What I'm looking for is a macro that will compare Column A in Workbook1!Sheet1 to Column B in Workbook2!Sheet1, and then add any differing values to Workbook1!Sheet1. Currently I do this by hand, copying Column B from Workook2 into Workbook1!Sheet1 and removing duplicates.

I then need the macro to run a VLOOKUP type search, using the value in Column A as the search value and returning Columns C, G and H from Workbook2!Sheet1 to Workbook1!Sheet1 columns B, C and D respectively. Currently I use (translation from Finnish excel, formulas may differ a little): =VLOOKUP(Sheet1!A1; [Workbook2.xlsx]Sheet1!$B:$H; 2; FALSE).

The issue with this, is that the raw data changes each week. The report may not contain the same values each week so the VLOOKUP occasionally results in an error, meaning I lose the notes. I would need the macro to leave the original data in tact if the search value is not found..

Once again, I'd really appreciate any help you guys can give me!

Regards,
notski

[Richard Buttrey]

Hi,

If I've understood you correctly, can't you just wrap your VLOOKUP() inside an =IFERROR() ? i.e.

Please Login or Register  to view this content.

Regards

[notski]

Hi,

Thanks for your reply. I apologise, I may have butchered my explanation a little.. The way this whole thing is constructed means the formula would need to be placed in Sheet1!A1, so that formula would end up referring to its own cell.

Here's what Workbook1!Sheet1 looks like:

Please Login or Register  to view this content.

And here's Workbook2!Sheet1

Please Login or Register  to view this content.

If possible, I'd like a command button Workbook1!Sheet1 that would check the CustID column and add "non-duplicates" (is that a word?) from Workbook2 to Workbook1, then update "Notes" and "Notes2". In this example, Name3 and Name4 did not have any notes attributed to them when the report was generated. If I then add new notes workbook2 through out the week, I would need the macro to update and store all the information on Workbook1 so it would look like this once the command is executed:

Please Login or Register  to view this content.

I hope this helps clarify what I'm looking for. Can this be done using formulas? I'm hoping a macro isn't necessary as I don't have the skills to write one, does anyone have any scripts that might be applicable?