Selected Answer
Abdo,
In the Discussion point above, you clarified that you're looking for any change in price.
The problem is that you create your dictionary item to INCLUDE the quantity from a(i, 4)) so different quantities attach to new dictionary items. Better (in my opinion) to test if the price has changed from that created (or modified) in the dictionary item. That's done in the attached file and code below (changes in bold):
Sub test()
Dim a, e, w, i As Long, n As Long, txt As String
a = Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
' exclude the quantity a(i, 4) from dictionary item
'txt = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
txt = Join(Array(a(i, 2), a(i, 3), Chr(2), Chr(2)))
If Not .exists(txt) Then
'2=Item, 3=ID, 4=Brand,5=Qty
ReDim w(1 To 7)
w(2) = a(i, 2): w(3) = a(i, 3): w(4) = a(i, 4): w(5) = a(i, 5)
Else
w = .Item(txt)
' only update if the price is different
If w(5) <> a(i, 5) Then
' write current quantity to element 4
w(4) = a(i, 4)
' write previous price to element 6
w(6) = w(5)
' add last price to element 5
w(5) = a(i, 5)
w(7) = (w(5) - w(6)) * w(4)
End If
End If
.Item(txt) = w
Next i
For Each e In .keys
If (IsEmpty(.Item(e)(7))) Or (.Item(e)(7) = 0) Then
.Remove e
Else
n = n + 1: w = .Item(e): w(1) = n: .Item(e) = w
End If
Next
If n > 0 Then a = Application.Index(.items, 0, 0)
End With
With [j2]
.CurrentRegion.Offset(1).ClearContents
.CurrentRegion.Offset(1).Borders.LineStyle = xlNone
If n > 0 Then
.Resize(n, 7) = a
.CurrentRegion.Borders.Weight = 2
End If
End With
End Sub
Note that my sequence is different from yours (columns M and N are now titled "Last Price" and "Previous" respectively.). Column P is positive if the Last price is higher than the previous.
Also it reports the item BTR-122 + FD/BTOOL22 where the price changed- that was excluded from your "Right Result" table.
Hope this fixes things for you (or you can swap the columns M and N to suit you).
