Compare row of data from different workbooks using another workbook - excel vba

  • Hello.


    I have a host excel workbook which contains all the VBA Codes. When I click a button there, it must compare two workbook's data and generate a report file.


    Workbook Data 1 contains this:


    Workbook Data 2 contains this:


    ID 1 must only be compared to ID 1 and so on thus generating a report file:


    The Workbook Data 2 offsets and adjusts its placement in the row if they have NO MATCH.

    Then ALIGNS to the Workbook Data 1 if the ROW of data are all the SAME with a REMARKS on the last column as MATCH.


    I have tried making this code and this is as far as I have gone to:

    In this code, I tried pasting the data of two workbooks to one workbook but I can't seem to know how to compare them from there.

    Please help anyone. Thank you.

  • It's hard to work with pictures. It would be easier to help if you could attach copies of your files.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    This works like a charm! THANK YOU SO MUCH! :love::saint:

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    Good day again sir!
    Is there a way for me to be able to show a save as prompt to save as the report.xlsx in the DataCompare folder so it wont be populated and will remain as a template?


    Also, how can I remove the data rows of File2 in the Report.xlsx file when it has no match? so that it just exists on the last part of the column?


    Thank you!!!!

  • You need to create a proper template with the extension .xltm. These files cannot be overwritten and you save a workbook created from them


    How to use Excel Templates

  • You need to create a proper template with the extension .xltm. These files cannot be overwritten and you save a workbook created from them


    How to use Excel Templates

    so from this line;

    Workbooks.Open ThisWorkbook.Path & "\" & "DATACOMPARE\Report.xlsx"


    I will just make a xltm file and change it to;

    Workbooks.Open ThisWorkbook.Path & "\" & "DATACOMPARE\Report.xltm"


    am i right sir? :)

  • The macro could save the report.xlsx workbook using a different name, perhaps by adding the date to the name.

    Also, how can I remove the data rows of File2 in the Report.xlsx file when it has no match? so that it just exists on the last part of the column?

    Are you saying you want to delete the rows with blank cells in column K?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • No you create a blank workbook with all the formatting, etc in. Then save it as a template file. This is basic Excel not VBA.


    When the template is opened it creates a copy of the template for you to work with.

  • This will work fine for me sir royUK :)


    Can you help me with my other problem sir? huhu

    I want to remove the rows from the right side data that has no MATCH and NO MATCH values

  • The macro could save the report.xlsx workbook using a different name, perhaps by adding the date to the name.

    Are you saying you want to delete the rows with blank cells in column K?

    Yes sir. It is just like cutting the data with no match and then paste it below leaving its place blank :D

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    Is it possible sir that it won't delete the entire row?

    Just the row starting from G to J?

  • Try:

    I wanted it to look like this but I cannot find a way to code it. Please help :(

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    This is a nice work!!! But I really want to make my data look like this:
    :( :( :(

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!