Iterate chart creation

I have manually created two sets of charts for two of the "ID" values shown in Column A. They represent example chart data ranges and formatting I'd like to replicate for the remaining IDs (see attached spreadsheet).

I am in need of code that will replicate the set of charts (one chart, one scatter chart) for each unique ID based on the range of values in Column C and D associated with each ID.

The desired result is 8 sets of charts that appear as 3 sets on each of the first two pages and 2 sets on the 3rd page.

For the line chart, the min value of the y-axis is fixed at 10, while the max value of the y-axis is allowed to vary based on the max value of the input y-axis data range. The date range on the x-axis does not vary among the different IDs.

The min/max values for the x- and y-axis of the scatter charts are fixed at 10 and 50 to accommodate all possible values in Columns C and D.

Many thanks in advance for your help.
charts.xlsx
LVL 1
dougf1rAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
I think we got it this time. I changed the color for you to see the trendline.
gowlfow
charts-V02.xlsm
0
 
dougf1rAuthor Commented:
Although there are only 8 Unique ID's in this spreadsheet, I will apply the solution to more than 250 Unique ID's.

Any help is very much appreciated.
0
 
gowflowCommented:
You still need some help on this one ?
If yes I hv couple of questions:
1) for each unique id you have several dates but the shown ones are 1 per week is this always like this ?
2) what is the data for the second chart the scattered point just mention the range

gowflow
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
dougf1rAuthor Commented:
1) Each unique id has a date range from 6/23/2012 to 8/15/2012 (except for the first id which starts on 6/24/2012). Values are not once per week, they are once per day.

2) I am not exactly sure what you mean by "what is the data for the second chart the scattered point just mention the range". The data for the second chart is from Columns C and D on the "Data" sheet.
0
 
gowflowCommented:
ok let me work on something. It will go probably for next year .... you will need to be patient it is a loooog time. :) Happy New year for you and your loved once.
gowflow
0
 
dougf1rAuthor Commented:
Many thanks for helping out with this. Happy New Year to you as well!
0
 
dougf1rAuthor Commented:
...I should also note that the date ranges associated with the Unique IDs in my other data sets (for which I will also apply the solution too) are not all the same length as those that are included in this example workbook.

The solution will be most useful if it can accommodate any number of dates (records) for a given Unique ID.
0
 
gowflowCommented:
Well the most important is to be able to sort the sheet that contain the data by the ID column then by the Date would this be a problem if dates are always ascending or you need a different sorting ?

By the way the macro will take care of sorting just wondered if you need the dates to be ascending or descending

gowflow
0
 
dougf1rAuthor Commented:
The dates would need to be ascending. Thank you for checking.
0
 
gowflowCommented:
ok tks working on it ... slowly ... but surely.
will hopefully revert soon. Tks ur patience
gowflow
0
 
gowflowCommented:
Hi

Finally here it is.

1) Download the file and activate macros.
2) I renamed your original sheets CHARTS to Original CHARTS so you can see it.
3) Activate macro CreateCharts and see the results. (if the sheet charts does not exist it will created or else it will delete its content and recreated based on data in sheet Data.
4) I added in sheet Data Col H1 to I2 0 and 50 which are the values for the line in scattered. pls make sure you do not remove those 2 rows or else the line will not work.


Appreciate your comments.
gowlfow
charts.xlsm
0
 
dougf1rAuthor Commented:
Thanks gowlfow! The macro works well.

However, there are a few modifications that are necessary:

1) remove the horizontal grid lines on both charts
2) For the plot on the right, change the chart type from "Scatter with Straight Lines and Markers" to "Scatter with Only Markers"
3) For the plot on the right, include a linear trendline and display the equation and R-squared on the chart
0
 
gowflowCommented:
ok noted.

I thought I gave you what you posted !!!

anyway
ok for 1 can remove gridlines
for 2 will need to see as not that obvious when it comes to vba !!!

Now the cherry on the cake is 3)
I do not understand what you want can you explain pls its almost chineese to me !!!

I only hope you appreciate the amount of time put in this it is not a piece of cake and I have been sitting on this for the past 72 hours !!!

gowflow
0
 
dougf1rAuthor Commented:
Your efforts are very very much appreciated!

With regard to number 3... if you look at the Original Charts tab, there is an equation and and R-Squared value shown in the upper left corner of the scatter plot. This is generated manually by:

1) Right-click on the data series and select "Add Trendline..."
2) Select "Linear" for the Trend/Regression Type
3) Mark the check boxes for "Dispaly Equation on chart" and "Display R-squared value on chart"

I am not familiar with how this would be automated with VBA.

Many thanks for your help gowflow.
0
 
dougf1rAuthor Commented:
This spreadsheet contains a macro that automates scatterplots which are very similar to the plots on the right that you have generated. I did not generate this code.

This VBA code may help with adding the Trendline, Equation, and R-Squared (#3 above) to the plot in the macro you have built.
VBA-For-Trendline.xlsm
0
 
gowflowCommented:
Excellent for what you explained and posted as I looked everywhere to get these graphs going. It is my second attempt ever in graph producing so had to lookup a lots of search as it is very complicated and not straight forward.

I will ask you to be patient, don't worry will get to shore.
gowflow
0
 
gowflowCommented:
tks your info you saved me lots of research on an issue that I totally ignore !!!

Pls check out this version.
gowflow
charts-V01.xlsm
0
 
dougf1rAuthor Commented:
Very nice! The only other thing that would be useful is to have the Chart Type as "Scatter with Only Markers" (rather than with the lines), for the charts on the right.

Please let me know if you think this is possible or not.

The example code I provided is using the "Scatter with Only Markers" Chart Type. Perhaps this is useful?
0
 
dougf1rAuthor Commented:
Excellent!

Huge thanks for your help with this. I hope the extra time spent researching will be useful to you in the future.

Cheers,
dougf1r
0
 
dougf1rAuthor Commented:
I plan to post another question (tomorrow) that is related to the same data set. This one will likely be quicker to solve and I have existing code that can be of help.

It would be great to work with you again. Please keep a look out if you can.
0
 
gowflowCommented:
sure and glad I could help. Do not hesitate to post a link here for any question you may need help with,
Rgds/gowflow
0
 
dougf1rAuthor Commented:
The new question is here: http://rdsrc.us/fmIvRs
0
 
gowflowCommented:
ok done. But I guess someone beat me on the time !!! :)
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.