Comparision of Excel sheets

Closed
pws - May 30, 2012 at 02:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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!
Related:

3 responses

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:

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

Little more details would have been helpful. Let me know if it worked.
0
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
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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,
Trowa
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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,
Trowa
0