A
allie357
I have a spreadsheet will a large amount of invoice numbers, some of
which are multiple occurrences of the same number. I need to count the
duplicates as one unique record and sum but I need to sum the total $
amount of each amount attached to each occurrence. For Example, say
Invoice Number W234678 has 10 occurrences and corresponding amounts. I
need the amounts to be added to give a total amount for that number
and then have Invoice Number W234678 added to the count as one
record.
I had this code kindly borrowed from this board which helped me find
the duplicates but it is not meeting my needs.
Thanks in advance for any help!
which are multiple occurrences of the same number. I need to count the
duplicates as one unique record and sum but I need to sum the total $
amount of each amount attached to each occurrence. For Example, say
Invoice Number W234678 has 10 occurrences and corresponding amounts. I
need the amounts to be added to give a total amount for that number
and then have Invoice Number W234678 added to the count as one
record.
I had this code kindly borrowed from this board which helped me find
the duplicates but it is not meeting my needs.
Thanks in advance for any help!
Code:
Dim rCell As Range, rRng As Range, vKey, lrow As Long
Set rRng = Range("F2:F199")
With CreateObject("Scripting.dictionary")
.comparemode = vbTextCompare
' load the info
For Each rCell In rRng
If Not .exists(rCell.Value) Then _
.Add rCell.Value,
Application.WorksheetFunction.CountIf(rRng, rCell.Value)
Next rCell
' Write the result in columns J:K
lrow = 2
For Each vKey In .keys
If .Item(vKey) > 1 Then
Cells(lrow, "J") = vKey
Cells(lrow, "K") = .Item(vKey) - 1
lrow = lrow + 1
End If
Next vKey
End With
End Sub