Solved

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

Posted on 2016-10-28

1 solution

51 Views

Last Modified: 2016-11-08

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

Comment

Question by:Bharat Guru

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 52

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 500 total points

ID: 418640532016-10-28

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
            dict.Add c.Offset(, 2).Value, c.Offset(, 2).Value
        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

Open in new window

Regards

Author Closing Comment

by:Bharat Guru

ID: 418793002016-11-08

Thanks

Featured Post

Question has a verified solution.

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

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

Custom Document Properties in Excel

Article by: Martin

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.