Comparision of Excel sheets [Closed]

Report
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
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

Posts
22
Registration date
Thursday April 19, 2012
Status
Member
Last seen
April 16, 2013
3
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:

=IF(A2=Sheet2!A2,"Match",Sheet2!A1)

Little more details would have been helpful. Let me know if it worked.
Posts
2
Registration date
Sunday February 9, 2014
Status
Member
Last seen
February 11, 2014

Thanks i applied your formula for comparison of 2 sheets data
It WORKED..

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
regards
ansharma
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Ansharma,

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

Best regards,
Trowa
Hi,

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.
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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,
Trowa