Solved

# Iterate chart creation

Posted on 2013-12-20
Medium Priority
201 Views
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.

charts.xlsx
0
Question by:dougf1r
• 12
• 11

LVL 1

Author Comment

ID: 39742135
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

LVL 31

Expert Comment

ID: 39743836
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

LVL 1

Author Comment

ID: 39746344
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

LVL 31

Expert Comment

ID: 39746631
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

LVL 1

Author Comment

ID: 39746654
Many thanks for helping out with this. Happy New Year to you as well!
0

LVL 1

Author Comment

ID: 39752624
...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

LVL 31

Expert Comment

ID: 39753288
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

LVL 1

Author Comment

ID: 39753936
The dates would need to be ascending. Thank you for checking.
0

LVL 31

Expert Comment

ID: 39757643
ok tks working on it ... slowly ... but surely.
will hopefully revert soon. Tks ur patience
gowflow
0

LVL 31

Expert Comment

ID: 39758770
Hi

Finally here it is.

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.

gowlfow
charts.xlsm
0

LVL 1

Author Comment

ID: 39759716
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

LVL 31

Expert Comment

ID: 39759897
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

LVL 1

Author Comment

ID: 39759998
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

LVL 1

Author Comment

ID: 39760027
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

LVL 31

Expert Comment

ID: 39760099
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

LVL 31

Expert Comment

ID: 39760329
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

LVL 1

Author Comment

ID: 39760458
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

LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 39760681
I think we got it this time. I changed the color for you to see the trendline.
gowlfow
charts-V02.xlsm
0

LVL 1

Author Comment

ID: 39760843
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

LVL 1

Author Comment

ID: 39760846
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

LVL 31

Expert Comment

ID: 39761197
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

LVL 1

Author Comment

ID: 39762359
The new question is here: http://rdsrc.us/fmIvRs
0

LVL 31

Expert Comment

ID: 39762733
ok done. But I guess someone beat me on the time !!! :)
gowflow
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with â€¦