Sub PetrosGeorgilas()
Dim ws As Worksheet, ws2 As Worksheet
Dim w As Long, x As Long, y As Long, z As Long
Dim rcell As Range, t As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set ws = Sheets("input1")
Set ws2 = Sheets("input2")
w = ws.Range("A" & Rows.Count).End(3).Row
x = ws2.Range("A" & Rows.Count).End(3).Row
ws.Columns(1).Insert
ws2.Columns(1).Insert
ws.Activate
With ws.Range(Cells(2, "A"), Cells(w, "A"))
.Formula = "=CONCATENATE(B2,C2)"
.Value = .Value
End With
ws2.Activate
With ws2.Range(Cells(2, "A"), Cells(x, "A"))
.Formula = "=CONCATENATE(C2,E2)"
.Value = .Value
End With
ws.Activate
For y = 2 To w
For z = 2 To x
If ws.Cells(y, 1) <> ws2.Cells(z, 1) Then
ws.Cells(y, 2).Copy Sheets("Output").Range("C" & Rows.Count).End(3)(2)
ws.Cells(y, 3).Copy Sheets("Output").Range("D" & Rows.Count).End(3)(2)
End If
Next z
Next y
ws2.Activate
For y = 2 To x
For z = 2 To w
If ws2.Cells(y, 1) <> ws.Cells(z, 1) Then
ws2.Cells(y, 3).Copy Sheets("Output").Range("C" & Rows.Count).End(3)(2)
ws2.Cells(y, 5).Copy Sheets("Output").Range("D" & Rows.Count).End(3)(2)
End If
Next z
Next y
ws.Columns(1).Delete
ws2.Columns(1).Delete
With Sheets("Output")
w = .Range("C" & Rows.Count).End(3).Row
For y = 2 To .Range("C" & Rows.Count).End(3).Row
For z = y + 1 To .Range("C" & Rows.Count).End(3).Row
If .Cells(z, 3) = .Cells(y, 3) And .Cells(z, 4) = .Cells(y, 4) Then
.Range(.Cells(z, 3), .Cells(w, 3)).Replace .Cells(y, 3).Value, "", xlWhole
.Range(.Cells(z, 4), .Cells(w, 4)).Replace .Cells(y, 4).Value, "", xlWhole
End If
Next z
Next y
For Each rcell In .Range(.Cells(2, "C"), .Cells(w, "C"))
If rcell.Value <> "" And rcell.Offset(, 1).Value = "" Then
Set t = ws.Columns(1).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not t Is Nothing Then
rcell.Offset(, 1).Value = t.Offset(, 1).Value
End If
Set t = Nothing
Set t = ws2.Columns(3).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not t Is Nothing Then
rcell.Offset(, 1).Value = t.Offset(, 2).Value
End If
Set t = Nothing
End If
Next rcell
For Each rcell In .Range(.Cells(2, "D"), .Cells(w, "D"))
If rcell.Value <> "" And rcell.Offset(, -1).Value = "" Then
Set t = ws.Columns(2).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not t Is Nothing Then
rcell.Offset(, -1).Value = t.Offset(, -1).Value
End If
Set t = Nothing
Set t = ws2.Columns(5).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not t Is Nothing Then
rcell.Offset(, -1).Value = t.Offset(, -2).Value
End If
Set t = Nothing
End If
Next rcell
w = .Range("C" & Rows.Count).End(3).Row
.Range(.Cells(2, "C"), .Cells(w, "D")).SpecialCells(4).Delete xlUp
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Bookmarks