Go Premium for a chance to win a PS4. Enter to Win

x
Solved

excel how to add group by average to bottom of the page

Posted on 2016-10-28
Medium Priority
65 Views
I have excel sheet where I want to group by Location at bottom of page
ProductID, Product, Location, price
1     Product1, NY , 10
2     Product2, NY , 20
3     Product3, NJ , 10
3     Product3, NJ , 20
3     Product3, NJ , 30

Out put should be
ProductID, Product, Location, price
1     Product1, NY , 10
2     Product2, NY , 20
3     Product3, NJ , 10
4     Product3, NJ , 20
5     Product3, NJ , 30
Average     NY   15
Average     NJ   20
Grand Average          18
0
Question by:Bharat Guru

LVL 53

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 41864053
Hi,

pls try
``````Sub macro()
Dim Res() As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False

For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
If Not dict.exists(c.Offset(, 2).Value) Then
End If
Next

Res = dict.Items()
For Idx = 0 To UBound(Res)
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lastRow).Offset(Idx + 1, 1) = "Average"
Range("A" & lastRow).Offset(Idx + 1, 2) = Res(Idx)
Range("A" & lastRow).Offset(Idx + 1, 3).FormulaR1C1 = "=averageIF(R2C3:R" & lastRow & "C3,RC[-1],R2C4:R" & lastRow & "C4)"
Next
Range("B" & Rows.Count).End(xlUp).Offset(1) = "Grand Average"
Range("D" & Rows.Count).End(xlUp).Offset(1).Formula = "=average(R2C4:R" & lastRow & "C4)"
Application.ScreenUpdating = True

End Sub
``````
Regards
0

Author Closing Comment

ID: 41879300
Thanks
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month7 days, 4 hours left to enroll