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
2
Medium Priority
?
65 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
0
Comment
Question by:Bharat Guru
2 Comments
 
LVL 53

Accepted Solution

by:
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
            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
0
 

Author Closing Comment

by:Bharat Guru
ID: 41879300
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question