VBA - extract sum, average on range of data when cell value changes

[sh14]

Hello,

I have hundreds of rows of data sorted by Col1 similar to this.

[pre]

Col1	Col2	Col3	Col4	Col5
Data1	1	1	1	1
Data1	2	2	2	2
Data1	3	3	3	3
Data2	1	1	1	1
Data2	2	2	2	2
Data3	1	1	1	1
Data4	1	1	1	1
Data4	2	2	2	2
Data4	3	3	3	3
Data4	4	4	4	4
Using VBA, I want to extract/summarize the data into a new spreadsheet with sum, average, min & max calculations each time the value in Col1 changes. My desired output is this:

Col1	Sum(Col2)	Min(Col3)	Max(Col4)	Average(Col5)
Data1	6	1	3	2
Data2	3	1	2	1.5
Data3	1	1	1	1
Data4	10	1	4	2.5

[/pre]
How do I do this in VBA? Any help would be greatly appreciated. Thanks!

 

[Luke M]

This will be MUCH easier if you just use a PivotTable rather than VBA. Creating the PivotTable, Col1 goes in the Row Labels, and the other columns go in the Data Items area. You can then change each data item's Value Field setting to Sum/Min/Max/Average.


PivotTable are awesome at handling large amounts of data, and do the calculations quickly. The only disadvantage is that you need to refresh the table to get the "latest and greatest" data.

 

[DaveTurton]

Could do a bit of both:


make your pivot table then paste the following in the sheet code of the sheet containing the data:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.RefreshTable

Next pt

Next ws

End Sub

[/pre]

Then whenever you select any cell on that sheet any pivot table in your workbook will refresh.


EDITED

When embedding text within backticks it's easier to adjust it in the VBA editor first, then copy and paste it here; if not, it's almost impossible to get it correctly indented.

 

[DaveTurton]

Obviously you can change it to look in a specific range for selection change if you don't want the whole sheet.


E.g. to only refresh if a cell is clicked within the range A1:B10

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim pt As PivotTable
Dim ws As Worksheet

If Target.Column >= 1 _
And Target.Column <= 2 _
And Target.Row >= 1 _
And Target.Row <= 10 Then

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.RefreshTable

Next pt

Next ws

End Sub

[/pre]

 

[sh14]

Awesome suggestions folks!

I was quickly able to get the desired result using a pivot table. And I can automate the process using Dave's pivot table + VBA idea.


Thanks so much - this will save me a ton of time!

 

[Luke M]

Glad we could help out, thanks for letting us know.