How to calculate sum for unique values in a different column

[kanishkgarg]

Hi,

I need to make a macro which calculates the sum for each unique ID and paste it in a new workbook. My data looks something like this:

Date	ID	Currency	Quantity	Fees
11.10	2561	EUR	10	55
11.10	2562	EUR	10	54
12.10	2561	EUR	10	53
13.10	2561	EUR	10	65
14.10	2561	EUR	10	56
14.10	2562	EUR	10	98
14.10	2563	EUR	10	26
15.10	2561	EUR	10	46
15.10	2563	EUR	10	79
16.10	2562	EUR	10	49
17.10	2562	EUR	10	33

so for example, it should calculate all the fees for customer ID 2561 for all the days there is data and similarly, for the the other customers too and paste it as follows in the new workbook:

ID	Fees
2561	Sum of all fees which have 2561 in column B
2562	"
2563	"
2564	"

I tried the following code but it wasnt what i wanted exactly as it simple calculated values for same values consecutive values:

Sub SumValues()
    Application.ScreenUpdating = False
    Dim ID As Range, x As Long, i As Long: i = 1
    For Each ID In Range("B2", Range("B" & Rows.Count).End(xlUp))
        If ID <> "Sum" And ID.Offset(1) <> ID Then
            Rows(ID.Row + 1).Insert
            Range("B" & (ID.Row + 1)) = "Sum"
            x = Range("K2", Range("K" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas.Item(i).Cells.Count - 1
            Range("K" & ID.Row + 1).Formula = "=Sum(K" & ID.Row - x & ":K" & ID.Row & ")"
            i = i + 1
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

 

[JEC]

Do you actually mean a workbook or is it a worksheet?
I see you have office 365, it's easy with dynamic array formula's. Using a macro for this could be more complicated

Anyway, here is an example macro, writing the output to the same sheet.

Sub jec()
 ar = Cells(1, 1).CurrentRegion
  With CreateObject("scripting.dictionary")
    For i = 2 To UBound(ar)
      .Item(ar(i, 2)) = .Item(ar(i, 2)) + ar(i, 5)
    Next
  Cells(1, 10).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

 

[kanishkgarg]

Do you actually mean a workbook or is it a worksheet?
I see you have office 365, it's easy with dynamic array formula's. Using a macro for this could be more complicated

Yes I would like to paste this in a different workbook. I do know I haven't done this in my macro. I will need a macro due to some work requirements.

 

[kanishkgarg]

Do you actually mean a workbook or is it a worksheet?
I see you have office 365, it's easy with dynamic array formula's. Using a macro for this could be more complicated

Anyway, here is an example macro, writing the output to the same sheet.

Sub jec()
 ar = Cells(1, 1).CurrentRegion
  With CreateObject("scripting.dictionary")
    For i = 2 To UBound(ar)
      .Item(ar(i, 2)) = .Item(ar(i, 2)) + ar(i, 5)
    Next
  Cells(1, 10).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

Hi, thank you so much for the solution. However, i need to change some details on this. The fees column in my dataset is actually in column J. When i try to change ar(i,5) to ar(i,11) it doesnt work. Also, I would only like to paste data which have value more than zero. Is that possible?

Thanks in advance!

 

[kanishkgarg]

Hi, thank you so much for the solution. However, i need to change some details on this. The fees column in my dataset is actually in column J. When i try to change ar(i,5) to ar(i,11) it doesnt work. Also, I would only like to paste data which have value more than zero. Is that possible?

Thanks in advance!

I should mention, it is working for all columns except the one i am trying as this has some # N/As which i dont want to remove as those have some significance in my analysis.