Autosum duplicates within X-axis values when creating a chart

[Maikel]

Hey all,

I've been wondering. Is it possible to create a graph with the help of VBA to have it automatically add same categories and add values without having to make a new data-table in excel and use that as sourcedata instead?

sample:

24-2010 3
25-2010 5
24-2010 4
26-2010 6
24-2010 4
25-2010 2

to be treated if the sourcedata has been:

24-2010 11
25-2010 7
26-2010 6

so what im actually looking for is for a way to create a stacked column graph which shows all values of categories, but enables u to see the seperate records as well. This can be achieved by having some way to let excel not use multiple occurences of the same values on the X-axis.

Any help would be greatly appreciated.

The way of making a new datatable when trying to do this is what i used to do in the past but i'm almost certain it can be done without even with VBA.

[Andy Pope]

If those values are dates then it can be done with a named range calculation.

Although for the effort involved I would just use some cells on a worksheet.

[PhilC]

Maikel, Yes you can do exactly what you are asking about. It takes a little bit of additional coding for the chart but otherwise it's relativly easy. This is what I came up with, I arbitrarily chose the number 3 because that's how many categories you showed in your example. The array can be as many elements as you have categories. Hopefully this code makes sense. If you need additional clarification, let me know.

Phil C

[VBA]Sub CategorySumChart()
Dim cat(3) ' if you know the number of categories you have dimention an array
rws = ActiveSheet.UsedRange.Rows.Count ' determine range to get data from

For I = 0 To rws - 1 ' Step through each data point
elem = ActiveSheet.Range("$A$1").Offset(I, 0).Value ' retrieve the category
elemNum = Val(Left(elem, 2)) - 23 ' manipulate the category to produce a numerical representation
cat(elemNum) = cat(elemNum) + ActiveSheet.Range("$A$1").Offset(I, 1).Value ' create the sum foa a category
Next I

ActiveSheet.Shapes.AddChart.Select ' Create your chart

cns = ActiveChart.SeriesCollection.Count ' count the number of series in the chart
If cns < 3 Then ' Make sure the number of series and the number of categories is the same
For I = 3 To (cns + 1) Step -1 ' set up loop to add series, add one to end for the last run of the for next loop
ActiveChart.SeriesCollection.NewSeries ' add new series
Next I
End If

For I = 1 To 3 ' loop through each element
With ActiveChart
.ChartType = xlColumnStacked ' set the chart type
catName = CStr(I + 23) & "-2010" ' rebuild category name
.SeriesCollection(I).Name = catName ' set the category name with the data
.SeriesCollection(I).Values = cat(I) ' load the data value for the category
.SeriesCollection(I).XValues = Array("Total of Categories") ' rename the summation with one element array
End With
Next I
End Sub
[/VBA]