Need to Compare 2 sheets in Excel and copy paste diff in new 3 sheet

[Aswinraj]

Hi Friends,

I have an excel file where we use to do manual work of Comparing 2 different sheets together and find the Mismatches and Duplicates between two sheets and Copy paste those differences in another sheet in Same Excel.

Ie., In Excel i have 4 Sheets - WAAG, PPMC, CADO, Headcount comparison.
Each sheet contains Employee ID as common factor.
When i run the macro it has to vlookup two sheets based on employee ID and copy paste the mismatch in Headcount comparison sheet.

For clear understanding the comparison should be as shown in picture below. Attached excel too. Please help me..,

 

[Derek McGill]

Here is a reply to this from another forum.

 Sub compare()
Sheets(3).Activate 'Go to sheet 3
Cells.Clear 'and clear all previous results

Range("a1").Select'set cursor at the top

Sheets(1).Activate 'go to sheet 1
Range("a1").Select'begin at the top



Dim search_for AsString'temp variable to hold what we need to look forDim cnt AsInteger'optional counter to find out how many rows we found

DoWhile ActiveCell.Value <>""'repeat the follwoing loop until it reaches a blank row

search_for = ActiveCell.Offset(0,1).Value 'get a hold of the value in column B

Sheets(2).Activate 'go to sheet(2)

OnErrorResumeNext'incase what we search for is not found, no errors will stop the macro

Range("b:b").Find(search_for).Select'find the value in column B of sheet 2

If Err <>0Then'If the value was not found, Err will not be zero

OnErrorGoTo0'clearing the error code

Sheets(1).Activate 'go back to sheet 1

r = ActiveCell.Row 'get a hold of current row index

Range(r &":"& r).Select'select the whole row

cnt = cnt +1'increment the counter

Selection.Copy 'copy current selection

Sheets(3).Activate 'go to sheet 3

ActiveCell.PasteSpecial xlPasteAll 'Past the entire row to sheet 3

ActiveCell.Offset(1,0).Select'go down one row to prepare for next row.

EndIf
Sheets(1).Activate 'return to sheet 1
ActiveCell.Offset(1,0).Select'go to the next row

Loop'repeat

Sheets(3).Activate 'go to sheet 3 to examine findings

MsgBox "I have found "& cnt &" rows that did not exist in sheet 2"

EndSub

 

[vletm]

...or something like this?