Summary report questions
 

I have created a spreadsheet, that links to another source data spreadsheet.
The source data spreadsheet is exported from SAP (our ERP system). All the
brains are programmed in the new spreadsheet that links to the exported file.
Of all the data that gets exported, there are two columns I'm interested in,
Inventory Value and Material Group.

What I would like to do is to group by the different material groups, sum
their Inventory Values, and sort the resulting sum in descending order
through VBA code. The results would then be displayed on a chart.

One problem is that every time the data source spreadsheet is refreshed, the
number and name of Material Groups can vary. Therefore, I can't hard-code the
material group names.

Of course, the same data is used on other worksheets for other graphs as
well. I am trying to code the operations listed in the Chart_Activate( )
event, and 'undo' all the sorting and grouping in the Chart_Deactivate( )
event.

Any ideas?

Here's an example:

Source Data:

Material Group Inventory Value .........
----------------------------------------------------------
Machined Metal $495.00
Printed Circuit Assy $2,364.93
Plastic and Rubber $416.00
Sheet Metal $625.00
Sheet Metal $250.00
Printed Circuit Assy $2,364.75
Printed Circuit Assy $2,187.24
Printed Circuit Assy $155.15



Summary Report (Inv $ Sum & Sort):

Material Group Inventory Value
----------------------------------------------------------
Printed Circuit Assy $7072.07
Sheet Metal $875.00
Machined Metal $495.00
Plastic and Rubber $416.00

Summary report questions
 

Why not use a pivot table instead of a whole pile of code. The pivot will do
that for you without any effort or code maintenance. It will handle the
aggregation and sorting along with a whole pile of other neat features.
--
HTH...

Jim Thomlinson


"Joel" wrote:

I have created a spreadsheet, that links to another source data spreadsheet.
The source data spreadsheet is exported from SAP (our ERP system). All the
brains are programmed in the new spreadsheet that links to the exported file.
Of all the data that gets exported, there are two columns I'm interested in,
Inventory Value and Material Group.

What I would like to do is to group by the different material groups, sum
their Inventory Values, and sort the resulting sum in descending order
through VBA code. The results would then be displayed on a chart.

One problem is that every time the data source spreadsheet is refreshed, the
number and name of Material Groups can vary. Therefore, I can't hard-code the
material group names.

Of course, the same data is used on other worksheets for other graphs as
well. I am trying to code the operations listed in the Chart_Activate( )
event, and 'undo' all the sorting and grouping in the Chart_Deactivate( )
event.

Any ideas?

Here's an example:

Source Data:

Material Group Inventory Value .........
----------------------------------------------------------
Machined Metal $495.00
Printed Circuit Assy $2,364.93
Plastic and Rubber $416.00
Sheet Metal $625.00
Sheet Metal $250.00
Printed Circuit Assy $2,364.75
Printed Circuit Assy $2,187.24
Printed Circuit Assy $155.15



Summary Report (Inv $ Sum & Sort):

Material Group Inventory Value
----------------------------------------------------------
Printed Circuit Assy $7072.07
Sheet Metal $875.00
Machined Metal $495.00
Plastic and Rubber $416.00

Summary report questions
 

You can do an advanced filter on the column of Material Groups to get a
unique list. Then use the sumif formula to get the sum. (all done with code
of course - use the macro recorder while you do it manually as a start).

--
Regards,
Tom Ogilvy

"Joel" wrote in message
...
I have created a spreadsheet, that links to another source data
spreadsheet.
The source data spreadsheet is exported from SAP (our ERP system). All the
brains are programmed in the new spreadsheet that links to the exported
file.
Of all the data that gets exported, there are two columns I'm interested
in,
Inventory Value and Material Group.

What I would like to do is to group by the different material groups, sum
their Inventory Values, and sort the resulting sum in descending order
through VBA code. The results would then be displayed on a chart.

One problem is that every time the data source spreadsheet is refreshed,
the
number and name of Material Groups can vary. Therefore, I can't hard-code
the
material group names.

Of course, the same data is used on other worksheets for other graphs as
well. I am trying to code the operations listed in the Chart_Activate( )
event, and 'undo' all the sorting and grouping in the Chart_Deactivate( )
event.

Any ideas?

Here's an example:

Source Data:

Material Group Inventory Value .........
----------------------------------------------------------
Machined Metal $495.00
Printed Circuit Assy $2,364.93
Plastic and Rubber $416.00
Sheet Metal $625.00
Sheet Metal $250.00
Printed Circuit Assy $2,364.75
Printed Circuit Assy $2,187.24
Printed Circuit Assy $155.15



Summary Report (Inv $ Sum & Sort):

Material Group Inventory Value
----------------------------------------------------------
Printed Circuit Assy $7072.07
Sheet Metal $875.00
Machined Metal $495.00
Plastic and Rubber $416.00

Summary report questions
 

If you want to try this way (which is a kind of cool solution) here is a way
to get a list of unique items. This sub generates a list of unique items and
pastes them onto a new sheet based on your selection on the original sheet.
This might make Tom's code idea a little easier to implement.

Private Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Scripting.Dictionary 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = New Scripting.Dictionary

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value < Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
End Sub
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

You can do an advanced filter on the column of Material Groups to get a
unique list. Then use the sumif formula to get the sum. (all done with code
of course - use the macro recorder while you do it manually as a start).

--
Regards,
Tom Ogilvy

"Joel" wrote in message
...
I have created a spreadsheet, that links to another source data
spreadsheet.
The source data spreadsheet is exported from SAP (our ERP system). All the
brains are programmed in the new spreadsheet that links to the exported
file.
Of all the data that gets exported, there are two columns I'm interested
in,
Inventory Value and Material Group.

What I would like to do is to group by the different material groups, sum
their Inventory Values, and sort the resulting sum in descending order
through VBA code. The results would then be displayed on a chart.

One problem is that every time the data source spreadsheet is refreshed,
the
number and name of Material Groups can vary. Therefore, I can't hard-code
the
material group names.

Of course, the same data is used on other worksheets for other graphs as
well. I am trying to code the operations listed in the Chart_Activate( )
event, and 'undo' all the sorting and grouping in the Chart_Deactivate( )
event.

Any ideas?

Here's an example:

Source Data:

Material Group Inventory Value .........
----------------------------------------------------------
Machined Metal $495.00
Printed Circuit Assy $2,364.93
Plastic and Rubber $416.00
Sheet Metal $625.00
Sheet Metal $250.00
Printed Circuit Assy $2,364.75
Printed Circuit Assy $2,187.24
Printed Circuit Assy $155.15



Summary Report (Inv $ Sum & Sort):

Material Group Inventory Value
----------------------------------------------------------
Printed Circuit Assy $7072.07
Sheet Metal $875.00
Machined Metal $495.00
Plastic and Rubber $416.00

Summary report questions
 

I forgot to mention to use this code you need to reference the "Microsoft
Scripting Runtime". If you need any help with that let me know...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

If you want to try this way (which is a kind of cool solution) here is a way
to get a list of unique items. This sub generates a list of unique items and
pastes them onto a new sheet based on your selection on the original sheet.
This might make Tom's code idea a little easier to implement.

Private Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Scripting.Dictionary 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = New Scripting.Dictionary

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value < Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
End Sub
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

You can do an advanced filter on the column of Material Groups to get a
unique list. Then use the sumif formula to get the sum. (all done with code
of course - use the macro recorder while you do it manually as a start).

--
Regards,
Tom Ogilvy

"Joel" wrote in message
...
I have created a spreadsheet, that links to another source data
spreadsheet.
The source data spreadsheet is exported from SAP (our ERP system). All the
brains are programmed in the new spreadsheet that links to the exported
file.
Of all the data that gets exported, there are two columns I'm interested
in,
Inventory Value and Material Group.

What I would like to do is to group by the different material groups, sum
their Inventory Values, and sort the resulting sum in descending order
through VBA code. The results would then be displayed on a chart.

One problem is that every time the data source spreadsheet is refreshed,
the
number and name of Material Groups can vary. Therefore, I can't hard-code
the
material group names.

Of course, the same data is used on other worksheets for other graphs as
well. I am trying to code the operations listed in the Chart_Activate( )
event, and 'undo' all the sorting and grouping in the Chart_Deactivate( )
event.

Any ideas?

Here's an example:

Source Data:

Material Group Inventory Value .........
----------------------------------------------------------
Machined Metal $495.00
Printed Circuit Assy $2,364.93
Plastic and Rubber $416.00
Sheet Metal $625.00
Sheet Metal $250.00
Printed Circuit Assy $2,364.75
Printed Circuit Assy $2,187.24
Printed Circuit Assy $155.15



Summary Report (Inv $ Sum & Sort):

Material Group Inventory Value
----------------------------------------------------------
Printed Circuit Assy $7072.07
Sheet Metal $875.00
Machined Metal $495.00
Plastic and Rubber $416.00