CODEWITHSUNDEEP
vbaexcel
evabb
evabb

Reputation: 405

How to perform the sum-group by function in VBA?

enter image description here Here is a table record every staff sales record in every months.

Every responsible staff in every district could be changed .E.g. District A: May :179 ->June: 220

Now , i want to sum all staff sales records from May-2016 to Jul-2016 . The range should be D3:I21.The range can be hard code.

Then paste the result on L3:M21 .

The sample output should be

179 1

186 7

222 4

S24 5

......

How to perform the SQL like select staffid ,sum(total) from table group by staffid in VBA ?

Remark :

Any idea is okay (no need code) .thank you so much

Is it able to do it in VBA? Or putting all data into two column in a virtual space .Then do the calculation later?

I must use VBA because :

This picture is just a example .Behind the real excel is a user form . The admin can select the fromdate and todate .Then ,generate the report according to the selected range .

Delete the row 23-Total is okay . Small modifciation of table structure can be considered .

Upvotes: 0

Views: 3287

Answers (1)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Hint :

Use Pivot Table by putting all the data into two columns: column staff and column sales.

Alternatively, copy ID staff in Range("D3:D21") to Range("L3:L21") and then put this formula in cell M3:

     =E3+INDEX(F$3:G$21,MATCH(L3,F$3:F$21,0),2)+INDEX(H$3:I$21,MATCH(L3,H$3:H21,0),2)

then drag cell M3 down to cell M21.

Anyway, you contradict yourself by saying, "Any idea is okay (no need code)" and then said "I must use VBA because". Which one do you want??

Edit:

The OP said the formula doesn't work properly, but it does work fine. Have a look this Excel file. Here is the VBA code that is implementing formula above:

Sub evabb()
Dim i As Long

With Sheets("Sheet1")
        For i = 3 To 21
            .Cells(i, "L") = .Cells(i, "D")
            .Cells(i, "M").Formula = "=E" & i & "+INDEX(F$3:G$21,MATCH(L" & i & ",F$3:F$21,0),2)+INDEX(H$3:I$21,MATCH(L" & i & ",H$3:H21,0),2)"
        Next
End With
End Sub

Upvotes: 1

Related Questions