First part was as before and used scripting dictionary to count the number of unique keys.
Then (out of simplicity) I added the formulas to the row of the newly formed columns and dragged down.
Last step of course was just to create the a line graph to visualize the results.
abousetta
P.S. Below is the vba code I used:
Option Explicit
Sub D()
Dim c As Range, R As Range, x&, Dic As Object, myKeys, myItems, myPrint
Set R = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dic = CreateObject("Scripting.Dictionary")
' Get unique values
With Dic
For Each c In R
.Item(c.Value & "|" & c.Offset(, 4).Value) = .Item(c.Value & "|" & c.Offset(, 4).Value) + 1
Next
myKeys = .Keys
myItems = .Items
ReDim myPrint(0 To .Count + 1, 1 To 3)
For x = LBound(myKeys) To UBound(myKeys)
myPrint(x, 1) = myKeys(x)
myPrint(x, 3) = myItems(x)
Next
Range("J2:L" & UBound(myPrint)) = myPrint
Columns("J:J").TextToColumns OtherChar:="|"
Range("J1:N1") = [{"EVENT_ID","FILTERED PRICE","COUNT","COST PRICE","BALANCE"}]
End With
' Sort and autofit
Columns("J:N").EntireColumn.AutoFit
With Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("K:K")
.SortFields.Add Key:=Range("J:J")
.SetRange Range("J:N")
.Header = xlYes
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set R = Nothing
Set Dic = Nothing
End Sub
Bookmarks