Previous Topic Next Topic

Compare two sheets based on a common ID in both sheets in col A

Solved/Closed
Ajay Dwivedi - Nov 13, 2014 at 01:34 AM
 Ajay Dwivedi - Jan 8, 2015 at 05:40 AM
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

Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 15, 2014 at 06:52 AM
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.
0
ajaydwivediji Posts 9 Registration date Tuesday October 30, 2012 Status Member Last seen January 5, 2017
Nov 26, 2014 at 07:37 AM
Apologies rizvisa1! I noticed your response today only

We Appreciate the initiative taken by you!

This was a perfect mechanism to get the required results. But I have a challenge here.

- The data I have contains large number or workbooks and each workbook again contains of lot of sheets in them.

- Going one-by-one is very time consuming and also makes the file heavier and heavier.

- I request you to kindly provide me a solution through a [Macro] that will help to ease out my team's pain to find the gaps/mismatches.

I hope that this is not too much complex.

Kindly advise.

Thank you and Best Regards - Ajay Dwivedi
0
ajaydwivediji Posts 9 Registration date Tuesday October 30, 2012 Status Member Last seen January 5, 2017
Nov 28, 2014 at 09:28 AM
May I have a kind attention to this request please.
Ajay
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 28, 2014 at 09:51 AM
Please explain further "large number of workbooks" How do you see in ideal case the macro to work ?
0
ajaydwivediji > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Dec 3, 2014 at 03:10 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 6, 2014 at 09:52 AM
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)
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How do I change a base location on Instagram?
rebelkids -
38nancy -

8 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses