Create multiple charts from one set of data (i.e. automate a repeating process)

[AlisonB]

Come to my rescue again!

I have a Excel 2000 worksheet showing customer satisfaction results
for our dealer network.

I want to give each individual dealer his results on a radar chart
that also shows the overall dealer network results – this way a dealer
can see where he performs better or worse than average (N.B. the
chart type must be a radar chart).

Can some tell me how to do this without having to manually select the
data for each dealer? If any suggestions involve using VB, please be
aware that I am a complete programming newbie and will need a true
idiot's step by step guide! There is an old post from Jon M. Peltier
on 16 August 2000 (my birthday!)that was in response to a similar
request but I don't know what to do with the code in his reply – i.e.
where to put it and how to adapt it to my data (sorry!).

I am willing to try any add-in etc. - any assistance would be greatly
appreciated

Kind regards,

Alison

 

[Jon Peltier]

Alison -

So you want to show two series, one always the overall and the other for
a specific dealer.

Create a dynamic range that selects a dealer based on a number, which
indicates the column or row number for the dealer's information. Use a
dropdown in the worksheet to enable easy selection of a particular
dealer. Make a chart with this range for one series and the overall
information for the other.

There are more details on this approach here:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[AlisonB]

Hi John,

Thanks for your reply.

Yes, I do want to show two series, one always the overall and the
other for
a specific dealer on each chart.

I have downloaded your zipped example file. I really like what you
have suggested – I can think of how I can use this in my work. What
you have suggested actually would be very useful for managers looking
at the different performances of the dealer network. It is a bit like
a pivot chart as opposed to a pivot table.

However, before I start to set everything up, is it possible using
your suggestion to produce a complete set of charts without selecting
each dealer first? I don’t want a dealer to be able to see the
results of another - I need to be able to send each individual dealer
his results only.

I must admit I expected to have to use some code as suggested in your
earlier post on 16.8.00. I think this would do what I want if I knew
how to use it! To give you some more background information, on a
regular basis I export the data from my survey software package as a
csv file to produce the results charts in Excel. The data is in the
same location on each file so I thought it would be a case of running
a macro against the csv file after I export the data each time.

Kind regards,

Alison

 

[Jon Peltier]

Alison -

Does the CSV have the data for all of the dealers, or only for each one.

Whichever, it's possible to open the CSV file, add the totals for all
dealers, then row by row, copy the row and the totals to a new workbook,
and make a chart of the two series. In pseudocode:

Open CSV file (it will open as a worksheet)
Insert row 1 and add formulas for totals
define a range as row with summary and next dealer's row
copy rows and paste in new workbook
create chart of the data
save workbook as dealer's name & date & .xls
distribute the workbooks.

Even if you don't know much VBA, you could turn on the macro recorder
and generate some pieces of it as you follow these steps manually. Then
streamline it, embellish it, and you're ready to go.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[AlisonB]

Hi John,

Yes, the csv has all the data I need – when I open it all I have to do
is rename it as an excel file and just sort it so that it is in
correct format to create my charts. I can use the macro recorder to
create the first chart for the first dealer. I need some help to
develop the macro to repeat for the next and subsequent dealers. It
is the “repeat” part I am stuck on - each dealer (there are literally
thousands) has a unique reference number so it should be possible to
identify the end of one dealer’s data and beginning of the next, I
just don’t know how.

Creating all the charts in one workbook would be fine – I don’t want
to have separate workbooks for each dealer.

Kind regards,

Alison

 

[Jon Peltier]

Alison -

When you import the csv file, is it already arranged into neat rows and
columns in the format you need to print? I'd expected each dealer to be
in a separate row. Otherwise how do you run totals or averages?

If the data is in nice rows, with the dealer's name or index in one
column, you can find the cell in that column corresponding to a
particular dealer, use that row for the series Y values range, then
export the chart with the dealer's name or index embedded in the filename.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______