Automatic chart through VBA with extensive data as base

Hi guys and gals,

I've got a bunch of data that I want to put in a chart - but it's a bit of a challenge.

Basically, row 1 contains week numbers, starting in column 5, then next week in column 8, next week in column 11 etc.
In column 5 below it is quantities of sales, in column 6 is the turnover, in column 7 is the margin percentage - then column 8 is qty, 9 turnover, 10 margin - etc.
In column 1 there's data we can ignore
In column 2 there's a category group number
In column 3 there's a product group number within that category group
In column 4 there's a brand

Now, what I'd like is this :
Per product group a chart / overview of the different brands and their TURNOVER in € (left Y axis) and MARGIN in percentage (right Y axis) per different week.

The raw data feed cannot be put in a different format.
The number of brands per product group differ and the number of product groups per category differ - every week there will be new raw data and in there, the number of weeks in total will differ.
I'd like to feed the raw data to a hidden sheet and have the charts alter based on loading new data.

It would be awesome if I could tick on/off the brands per chart, but that's a second question.

Example of raw data as picture attached - I'll try to upload a sample sheet asap.
Thanks for any help!

example data.jpg

Hi guys and gals,

attached an example file to work with - it would be much appreciated if anyone can help me! Thanks a lot in advance!

excelforum_example.xls

hereby an example of the graph as I expect it to turn out (more or less).

Now - I suck at graphs and would have no idea how to do it automatically based on the raw data file, but would appreciate the help to do this with a macro.
Thanks so much!

example_graph.jpg

Is my question too vague or the solution too difficult?

Hi Mad-Mizer, that's a decent idea - however, I want the chart to show on a different page.
Also, the number of weeks of data will differ and the number of products per sub-category (product group) will differ.
Basically I'd want a macro that would "draw" the charts per product group on the click of a button.
This will generate a whole bunch of charts, based on a different sheet of data. I think this should be a (relatively easy) possibility, but I have no idea how to go about it, cause I suck at charts.

Thanks!

All the charts can be on one seperate sheet.
I will load the raw data in a sheet called "data" (standard hidden).
Then let's say there's a sheet called "Charts" where all the charts per product group are.

From my example data file, there would be three charts :

048 FRUITS
072 NON-FISH
037 FISH

Per chart, there would be two Y-axis, one showing the percentage margin per product type (APPLE / PEAR / BANANA / PEACH / GRAPE for 048 FRUITS chart) and one showing the sales turnover.
The X-axis would be the weeks available in the raw data file. Oldest week will always start in column E, the last week (and thus last column) will be variable.

Chart name would be the product group name ("048 FRUITS").

I hope this makes things more clerar and someone will be able to help Thanks so much in advance!

Hello people,

I know I might seem incredibly impatient, but it's a bit important for me to get this working.
Is there anyone who could be of assistance, pretty please with sugar on top?

Hi JasperD,

I once wrote a lot of code to basically do what you need however I no longer work at the place where I wrote the code and no longer have access to it. I will say that it was no small feat to create it and took many hours.

I think your requirements will be the same in that it will take many hours to complete and I just don't have that kind of time. Perhaps an easier way would be to split out your data into different sheets, and then filter the data as I suggested earlier. This way there is no coding, it should be relatively simple once the data is split out.

Hi Mad-Mizer,

I appreciate your efforts to help me. Fact is - I think I can get the coding down, but to be honest, I'm an idiot with making charts (even without VBA) - I'd have a hard time just to make the chart I want...
If I can do that (or you could help me), then I can use the macro recorder and build on the code from there.. Any chances there?

Let me look at this - just posting to let OP know someone is looking at it, and to try to prevent lots of people duplicating effort. Should have something in a hour or so.

OKay, try the attached - run the TransformData macro to update everything, select the Product Group using the pivot chart buttons.


Sorry for the delay, got caught in traffic on my way home!

Hi Olly,

it LOOKS amazing!

But I run in some difficulties.
If I select another product group in the chart, the code "RebuildSub" errors out on the first line :

Please Login or Register  to view this content.

Error : "kan methode of gegevenslid niet vinden" which rougly translates to cannot find method or reference ; This will most likely be caused by that I use a Dutch version of Excel instead of an English version. Any suggestions to fix this?

Also - I see code to update the original "raw data" - is there a button to call that or is it run manually.

Thanks so much, Olly - it's ALMOST like I envisioned it!
(By the way, I changed the turnover columns in the chart to stacked instead of next to each other, but I can't imagine that changes the workings of the code, does it?)

Ah, sorry - a few Excel2013 references slipped in there!

Go to shChartData module, change this subroutine:

Please Login or Register  to view this content.

That should sort it. Test code works fully first, before making any further changes to chart.

I haven't built in any control shortcuts to the TransformData subroutine - it would be easy enough to add this to a custom menubar, or add a control button somewhere. Or you can just run it manually.

Hi Olly,

updating and chart writing works perfect so far.

Couple of Q's.
* If I update data, the old "brands" (ABALONE, APPLE) etc, stay selectable, even though they're not in the new data (surprisingly, we don't sell the brand Salmon )
* Is there a way to reset the brand filter when selecting a different product group, because if I select a brand that is in product group X but not in the next selected product group, I cannot change it back

Thanks again!

#Q1: In worksheet ChartData, pivot table ptChartData: Pivot Table Options > Data > Number of items to retain per field: choose NONE. Close options, refresh pivot table.

#Q2: run something like this to reset the Product Group filter:

Please Login or Register  to view this content.