How to create a bubble chart with 3 categories and by year

[shoesy4]

Hi,
I've been having trouble creating a bubble chart that I need, other than doing it manually with different tools.

I have a data set over a number of years by:
country (A thru O in this example);
volume;
market share.

I'm trying to recreate the visual that I mocked up in this worksheet (attached). I added 'number of units' to try and spread the bubbles in each year 'column' although I can't find a way to make that work.

I also want to add the country label to each bubble - things such as the vertical dotted lines I can add on post production.

Any ideas?

Thanks in advance - this is my first post!

Regards,

[shoesy4]

Hello, I seem to have a lot of views on this post but no replies - can anyone assist with the solution please?

Regards,
Mark.

[teylyn]

Hello,

Some condiserations:

You need a much simpler data layout unless you want to hand pick each series' values for X, Y and bubble size. It is not clear whether you want a series per country or a series per year. Please clarify.

The distribution of the bubbles on the X axis in your sample requires different numbers than the ones you have entered. If two bubbles are in the same X position, that means they need the same X value. None of your X values are the same, so the bubbles will all be aligned like a string of pearls.

Overall, you've had no response because you've thrown a huge task out there. You may want to break it into smaller steps or ask more concrete questions than "build this"

cheers, teylyn