Option Explicit
Sub Comparatif_Release()
Dim t1, t2, c
Dim d As Object
Dim i&, j&, l&
Dim f As Worksheet
Set f = Sheets("Sheet1")
With f
l = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
t1 = .Range("a3:e" & l).Value
t2 = .Range("g3:k" & l).Value
End With
Set d = CreateObject("Scripting.Dictionary")
For i = LBound(t1) To UBound(t1)
If t1(i, 1) & t1(i, 2) <> t2(i, 1) & t2(i, 2) Then
d(t1(i, 1) & ":" & t1(i, 2) & ":" & "Deleted community") = d(t1(i, 1) & ":" & t1(i, 2) & ":" & "Deleted community") & "Old Features :" & t1(i, 3) & Chr(10)
d(t2(i, 1) & ":" & t2(i, 2) & ":" & "New community") = d(t2(i, 1) & ":" & t2(i, 2) & ":" & "New community") & "New Features :" & t2(i, 3) & Chr(10)
Else:
If t1(i, 3) <> t2(i, 3) Then
d(t1(i, 1) & ":" & t1(i, 2) & ":" & "Features") = d(t1(i, 1) & ":" & t1(i, 2) & ":" & "Features") & "New Features: " & t2(i, 3) & ", Old Features: " & t1(i, 3) & Chr(10)
End If
If t1(i, 5) <> t2(i, 5) Then
d(t1(i, 1) & ":" & t1(i, 2) & ":" & "Rank") = "New Rank: " & t2(i, 5) & ", Old Rank: " & t1(i, 5) & ", Change percentage: " & Round((t2(i, 4) / t1(i, 4) - 1) * 100, 2) & "%"
End If
End If
Next i
With f
i = 3
For Each c In d.Keys
.Cells(i, "N").Resize(, 3).Value = Split(c, ":")
.Cells(i, "Q").Value = d(c)
i = i + 1
Next c
End With
End Sub