Compare two sheets based on a common ID in both sheets in col A [Solved/Closed]

Report
-
 Ajay Dwivedi -
Greetings Kioskea team,

First of all, my heartiest congratulations and thank you to the great Kioskea team that who have a mine of excellent solutions already available in their Forum and if by change not available then it just ask, it is with us in no time.

I have have went through tremendous macros to compare two sheets and they have awesome results.


But I have little tweak in my data for the comparison.

The Data:

I have two sheets [Before] and [After] containing data in A:AZ columns.
Please note that position of these column names [headings] are same in both the sheets.

First column contains an Id that is key to match both sheets.
Please note that the count of rows are different in both the sheets. It is possible that either an Id may be missing in the either sheets or may be appearing more than once.

My challenge is to compare both the sheets [Before] and [After] based on Id in Column A, and
a. Either highlight the cells that are [Not matching] in a column
OR
b. Display the mismatched results [highlighted cells with entire affected Row] in third sheet [Results]


Don't know I may be very demanding, but this will help me a lot to provide assistance to my team and seniors.

Appreciate to helping we non Tech people all around the world.

Best Regards - Ajay Dwivedi

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
I think you can use VLookup by doing vlookup in [BEFORE] and copy any result that is #NA to new sheet. and do so same in after.
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
Please explain further "large number of workbooks" How do you see in ideal case the macro to work ?
>
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

The term [Large number of workbooks] means, the count of excels are more than 50.
The columns in each excel contains pair of 15 fields.

This is a reason I requested for a Macro [to compare pair of fields] no mater if done one by one, instead of pivot.

Kindly advise.

Thank you and Best Regards - Ajay Dwivedi
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
If I understood you, you have 50+ workbooks. Each workbook has two two sheets BEFORE and AFTER. If that is the case, what I suggested still holds good. Only thing on top you need is a loop that goes for one workbook at a time in a directory(hopefully all workbooks are in one location)
>
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

Thank you Rizvisa,

Could it be possible to get me what you stated above [Only thing on top you need is a loop that goes for one workbook at a time in a directory(hopefully all workbooks are in one location)], as this will help me greatly. It is consuming a lot of time and efforts all around.

Kindly help.

Sincere Regards - Ajay Dwivedi
Posts
14
Registration date
Tuesday October 30, 2012
Status
Member
Last seen
January 5, 2017

Yes, Rizvisa.

50+ workbooks and 15x2=30 fields in each book. 15 before and 15 after.

Your understanding regarding the execution is absolutely what I had requested above.

Thank you and Sincere Regards - Ajay Dwivedi