Merging 2 or more Charts, or manually creating each bar

Hi!

I've been trying to look for a way to "merge" two or more bar charts, with no success over google nor MrExcel.
I'm trying to merge two bar charts into one.As said, one image > 1.000 words, here's an image of what i want:

merging-charts.png

Creating 3 series and making a stacked bar chart would do the job, but wouldn't fit my purposes, as I will be having more data in the nearest columns , concerning different years, and i want also to show the same chart for all of the columns. I also have the same need in other sheets with different charts, so it would be great to find a solution without having to reorganise the data, or creating auxiliary tables for each year.

As asked, here's a kick excel sheet which shows the data, what i'm obtaining, and what i want to get:
charts1615.png

To sum up:
Is there a way to build each bar in a chart manually selecting it?
Once i get to the solution, i would also like to ask excel to show the same chart, but getting the data from next column. Is there a way to do so?


Thanks in advance to everyone!

PD: I posted the same question in other forums, because it's been a while since i posted the first one and i didn't get the solution i wanted, so i tried in different communities. Once I get the solution i will post it in every thread so everyone can benefit from it.
It seems that i'm not allowed to post any URL for the moment as asked in a reply, so i the only way i figured out to do so is by attaching a text file with them.

After more than a day and no responses in either location, I will venture to respond. I think there is a good deal of uncertainty on our end trying to understand exactly what you want and exactly what your limitations are.

I also have the same need in other sheets with different charts, so it would be great to find a solution without having to reorganise the data, or creating auxiliary tables for each year.

What I see in your pictures are clustered and stacked bar charts, and the usual approach to a clustered and stacked bar chart is all about rearranging data in the spreadsheet -- often leading to auxiliary tables. In fact, I would go so far as to say that the hardest part of creating these charts is building the auxiliary table that would make creating the chart fairly easy. I think the first thing I would want to understand is why you are opposed to using an auxiliary table to build this chart.

I thought it might be a common issue, and there would be a way to manually tell excel, for each column,

The auxiliary table described is the easy way to tell Excel which value(s) go(es) in which stacked bar series. I'm sure there are other ways to do this that will avoid having the auxiliary table in the spreadsheet (probably something involving array constants stored in the chart rather than references to the spreadsheet).

I find that this kind of programming question is not usually about what is possible. I tend to be optimistic that, with enough time and effort and ingenuity, almost anything is possible. I find it usually comes down to more of a cost-benefit analysis. You've shown that the chart is quite easy to create using an auxiliary table. The question is more about is the benefit (eliminating the auxiliary table) worth the cost (more time/effort to create the chart and more time and effort to change/maintain the chart in the future). Before going to the extra effort of creating a chart without the auxiliary table, I am hoping to understand why you want to avoid the standard, easy way to do this.