Solved

Iterate chart creation

Posted on 2013-12-20
25
191 Views
Last Modified: 2014-01-07
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
0
Comment
Question by:dougf1r
  • 12
  • 11
25 Comments
 
LVL 1

Author Comment

by:dougf1r
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 29

Expert Comment

by:gowflow
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

by:dougf1r
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 29

Expert Comment

by:gowflow
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

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

Author Comment

by:dougf1r
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 29

Expert Comment

by:gowflow
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

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

Expert Comment

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

Expert Comment

by:gowflow
ID: 39758770
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
 
LVL 1

Author Comment

by:dougf1r
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 29

Expert Comment

by:gowflow
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

by:dougf1r
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

by:dougf1r
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 29

Expert Comment

by:gowflow
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 29

Expert Comment

by:gowflow
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

by:dougf1r
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 29

Accepted Solution

by:
gowflow earned 500 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

by:dougf1r
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

by:dougf1r
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 29

Expert Comment

by:gowflow
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

by:dougf1r
ID: 39762359
The new question is here: http://rdsrc.us/fmIvRs
0
 
LVL 29

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now