Macro based on Primary key

[aniletc37]

Hi,


I have data in sheet1 and sheet2.It will comparesheet1 data with sheet2 data based on empid.If empid matches
with sheet2 then chcek all the data for the empid whether it is matching or not.If any column values is mismatching for that particular id in sheet1 and sheet2
then highlight those unmatched columns with red background in both the sheets.If empid not present in any sheet then highlight that entire record with red color background color.Output Highlighted in bold color below.Sheet1 and sheet2 have more number of columns not only 4 columns .but sheet1 and sheet2 have same number of columns.

sheet1

Empid name loc age
1 a d 23
2 b e 21
3 c f 20
4 d g 22
5 e h 24
6 f i 25

sheet2
Empid name loc age
1 a d 23
2 b e 21
3 c f 20
4 f g 22
5 e k 24
7 f j 26

[jaslake]

Hi aniletc37

Place your Data in a Workbook then attach that Workbook.

[aniletc37]

Hi jaslake,

Please check the attached data for reference.I have added only 4 rows and 4 columns data for sample.But no of max columns should be 25 in sheet1 and sheet2. I need powerful macro because i'm having almost 250000 records in each sheet.So performance also one issue...

[jaslake]

Hi aniletc37

Performance IS going to be an issue...

[aniletc37]

Hi jaslake,

Can you provide code for this.I will check with less number of records.If it is take time also noproblem.in between it shows like time for comparing msgbox also noproblem

[jaslake]

Working on it....

[jaslake]

Hi aniletc37

This Code is in the attached and appears to do as you require...CTRL + x will fire the Code. Change Sheet Names as appropriate...

Please Login or Register  to view this content.

[aniletc37]

Hi jaslake,

Thx for the code.If add more columns it is giving error and some extra coulmns are creating in sheet1 and sheet2 as processed with 'X' also It is giving run time error autofilter method of range class failed.6th record is not identifying in sheet2 also not showing green colorbcz it is not present in sheet1.Check the attached data for reference.

Parameterization of columns is possible for this code.Like here we are considering Column A as key based on that we are checking data in sheet1 and sheet2.Instead of that while running macro it will ask for column name as Key to comapre in sheet1 and sheet2.if we give column numbers then it will comapre data based on those keys.

[jaslake]

Hi aniletc37

This Code handles Issue 1...had to change this line of Code

Please Login or Register  to view this content.

I'll need to think about Issue 2.

Please Login or Register  to view this content.

[jaslake]

Hi aniletc37

Regarding this...I have no clue what you want

Parameterization of columns is possible for this code.Like here we are considering Column A as key based on that we are checking data in sheet1 and sheet2.Instead of that while running macro it will ask for column name as Key to comapre in sheet1 and sheet2.if we give column numbers then it will comapre data based on those keys

Best I can tell you, try the attached. If it does not work you need to tell me why.

[aniletc37]

Hi jaslake,

Thx for the code.Is there any option to select multiple columnnames for comparing in userform?

[jaslake]

Hi aniletc37

I've written/rewritten the Code twice. Before I rewrite it again I'd like you to think and be clear about your needs. Identify and spell them out. Get back to me when you've done so.

[aniletc37]

Hi jaslake ,

I'm ok with that code.I need only one help.forms are not enabled in my office system.can you just give the code with out using forms.you have provided last time that code is enough.only problem with last time code is columns are more its creating some extracolumns like processd with x and runtime error. just rectify that issue and provide the code.

[jaslake]

Hi aniletc37

I don't know...you'll need to tell me if it's working. As before, CTRL + x will fire the Code.