Comparision of Excel sheets

pws - May 30, 2012 at 02:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 10, 2014 at 11:15 AM
I just want to ask for your help by providing me codes / references to compare the data in two worksheet, which it means to compare the data in sheet 1 and sheet 2 and display the comparing result in sheet 3, all the worksheet was in one same workbook. As the below code displaying the values for matching rows but i want the output if different values exist for same fileds in two sheets.

Sub compare()
Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As Range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As Range
Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet
Dim X As Long
Dim Y As Long
Dim C_1 As Range
Dim C_2 As Range
Dim C1 As Range
Dim C2 As Range

Set Sh_1 = ActiveWorkbook.Sheets("Master")
Set Sh_2 = ActiveWorkbook.Sheets("Inventory")
LastRow_1 = Sh_1.Range("A500").End(xlUp).Row
LastCol_1 = Sh_1.Range("A500").End(xlToLeft).Column
Set Data_1 = Sh_1.Range("A2").Resize(LastRow_1, LastCol_1)
LastRow_2 = Sh_2.Range("A500").End(xlUp).Row
LastCol_2 = Sh_2.Range("A500").End(xlToLeft).Column
Set Data_2 = Sh_2.Range("A2").Resize(LastRow_2, LastCol_2)
For Each C_1 In Data_1
For Each C_2 In Data_2
If Data_1 = Data_2 Then
If C_2 = C_1 Then
'found a cell on sheet2 that matched cell in sheet1
'now do what you need to do
C_1.EntireRow.Copy Destination:=Worksheets("New_Master").Range("A500").End(xlUp).Offset(1, 0)
C_2.EntireRow.Copy Destination:=Worksheets("New_Master").Range("A500").End(xlUp).Offset(1, 0)
End If
Next C_2
Next C_1
End Sub

Thanks inAdvance!

3 replies

Mehedad Posts 22 Registration date Thursday April 19, 2012 Status Member Last seen April 16, 2013 3
May 30, 2012 at 05:18 AM
A sample data would have been useful which would have showed what values are being compared. However, assuming that you need to copy a single Cell in Sheet 1 if the data is not matching, it can be done using formula:


Little more details would have been helpful. Let me know if it worked.
ansharma Posts 2 Registration date Sunday February 9, 2014 Status Member Last seen February 11, 2014
Feb 10, 2014 at 05:05 AM
Thanks i applied your formula for comparison of 2 sheets data

But I need COMMENTS that are in a Cell also as a PART OF PARAMETER
for comparison.In other words "EXACT MATCH" .of cell to cell

Please help me I am comparing 7 years data comments in a cell ( that was lost) just recovered with adhoc data (without Comments
Please help me
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 547
Feb 10, 2014 at 11:15 AM
Hi Ansharma,

Could you provide a little sample of your data how it is and how you foresee it.

Best regards,

I am looking for copying row values to sheet3 if the fileds in sheet1(coloumn A) is matching with filed in sheet2(coloumn A).But i need only difference values.

Lets take a look below:

Sheet1 contains :
columnA ColumnB

ABC 10
XYZ 20
LKJ 30

Sheet2 contains :
columnA ColumnB

XYZ 20
ABC 50
LKJ 30

Now i need in sheet 3 like below:
columnA ColumnB

ABC 10
ABC 50

Because ABC having different values in sheet1 and sheet2.
Please provide me a macro code so that we will get reports based on fileds to compare.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 547
Jun 11, 2012 at 10:22 AM
Hi pws,

Start by copying both columns of both sheets to the 3rd sheet and sort them alphabetically.

Data looks like:
ColA   ColB
Header     Header
ABC	10
ABC	50
LKJ	30
LKJ	30
XYZ	20
XYZ	20

Column C is used for formula:
Cell C2: =IF(A1&B1=A2&B2,1,0)+IF(A2&B2=A3&B3,1,0)
Drag formula down.

Filter data on "bigger then 0" and delete the visible rows.
You can then remove the filter and delete column C.

Best regards,