| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count duplicates as unique record, sum amounts?
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! 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
|
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unique record count with multiple criteria | Excel Worksheet Functions | |||
| Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
| Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
| Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
| Count number of unique items in a column that contains duplicates | Excel Worksheet Functions | |||
Threaded Mode
