Thread: Align small charts to columns in large chart

Hi, All

I have created a stacked column chart which may have between 1 and 10 columns, based on dynamic ranges. Each column may contain up to 75 data points representing one of five categories, again based on dynamic ranges. This means that I have a distribution of the five categories through the stack. Underneath each individual stacked column in the large chart I have a small column chart which shows the percentages for the five categories in the stacked column above. What I want to happen is for the small column charts to be automatically positioned under the correct stacked column of the large chart when the number of columns changes. I am also trying to control the gap width so for charts with few columns I dont have very wide bars.
I have been wrestling with this for some time, so any assistance would be gratefully received.
Thanks in advance.

Can you post an example of your chart. Even if it's one not based on dynamic ranges, so we can see how it looks.

Thanks for your interest Andy.
The attachment is a screenshot of how the chart is coming out at the moment.
The misalignment shows up more with only one or two columns. With ten columns on the stacked chart the small charts end up compressed together but a little more in line with the big columns.
Some suggestions on how to approach the problem would be really great.
Thanks.
John Taylor

See the attached.

It uses a combination chart to plot stacked columns and cluster columns.
You would also need to add xy-scatter series to create the 2 axis tickmarks and labels.

Just out of interest how should you read the stacked bar of the chart?

Many thanks, Andy.
I would never have arrived at that solution.
That achieved everything I was hoping to do except I have lost some minor funtionality in colouring the chart series. I found some code written by Jon Peltier that colours the chart series by the series name. This code works on the stacked bar columns but I have not been able to get it to work on the cluster charts. In my workbook I have a start-up sheet where the user may choose colours for the chart legend. These colours are carried across several sheets and charts, but not into the cluster charts (see attached picture). The chart data table is now set up the same way you showed in your example so the cluster chart data follows straight on after the stacked column data. I copied the range used for the colour selection to the headings of the cluster data, so I am sure that the names are identical. I don't understand why the code does not seem to count the new series. Can you help?

Jon's code as used by me is:

HTML Code: 

Sub ColorBySeriesName()
'   Colour chart series to match legend from first page
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range
    
    Set rPatterns = ActiveSheet.Range("ChtCol")
    ActiveSheet.ChartObjects("Chart 10").Activate
    With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
            Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
            .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
            .SeriesCollection(iSeries).Border.LineStyle = None
        Next
    End With
    End Sub

As an explanation to the chart and the stacked columns, the page illustrates a manufacturing process. The workers in the process are referred to as the operators. Each worker is allocated a portion of the manufacturing process to perform. The actions that the worker may do are summarised in the chart legend as "Waiting" for machines to finish, "Unloading or Loading" machines with new components, performing "Manual" work on the part, "Gauging" or inspecting the part and "Walking" from one machine to the next. The red horizontal line indicates how much time is allowed to make each part. If the sum of time as shown by the stack is above the line, then there is too much work for that operator to perform and he will not be able to produce enough parts. Waiting and walking are wasted time and the chart gives us an appreciation of where, and how much wasted time is hidden in each workers job function. Also, we can see where some tasks may be redistributed so that all workers total time falls below the red line, and the required number of parts can be made in time.

Thanks again, Andy.
That was the last little fix to finish off the chart. It works beautifully.
Your legend status is well deserved!
Regards.
John