Comparision of Excel data

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hi,
I am looking for comparing calues of sheet1 with sheet2 if the fileds in sheet1(coloumn A) is matching with filed in sheet2(coloumn A).
If the Field is matching in both the sheets then copy the values of both fields in sheet3(only mismatched data)

Lets take a look on below example:

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.
we will get reports based on fileds to compare.Please provide me a macro code.

Thanks in Advance.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
sheet1 and sheet2 have column headings in row no. 1

sheet1
hdng1 hdng2
ABC 10
XYZ 20
LKJ 30


sheet2

hdng1 hdng2
XYZ 20
ABC 50
LKJ 30


copy sheet1 data to new inserted worksheet "sheet4"

now run the macro and see sheet3


Sub test()
Dim r As Range, r1 As Range, unq1 As Range, cunq1 As Range, x As String
Dim filt As Range, c As Range, y
With Worksheets("sheet3")
.Cells.Clear
End With
With Worksheets("sheet2")
Range(.Range("a2"), .Range("a2").End(xlToRight).End(xlDown)).Copy
With Worksheets("sheet1")

.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial

Set unq1 = .Range("a1").End(xlDown).Offset(5, 0)
Set r = .Range("a1").CurrentRegion
r.Sort key1:=.Range("a1"), header:=xlYes
Set r1 = Range(.Range("a1"), .Range("A1").End(xlDown))
r1.AdvancedFilter xlFilterCopy, , unq1, True
Set unq1 = Range(unq1.Offset(1, 0), unq1.End(xlDown))
For Each cunq1 In unq1
x = cunq1.Value

r.AutoFilter field:=1, Criteria1:=x
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(12)
y = filt.Areas(1).Cells(1, 2)
'MsgBox y
For Each c In Range(.Range("B3"), .Range("B3").End(xlDown)).SpecialCells(12)
If c = y Then GoTo nextcunq1

filt.Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
GoTo nextcunq1
Next c
nextcunq1:
.AutoFilterMode = False

Next cunq1
End With
End With
Worksheets("sheet1").Cells.Clear
Worksheets("sheet4").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub