Use VBA to populate Line Charts (Was Scatter Charts)

In my workbook I have Sheet1 with some sample data.
Column A is the "PlanName", for every instance of "0250018_REV_E_1" I want to use data from Column C and D to populate Scatter Charts based on the PlanName of Column A.
For every PlanName change, create a new worksheet/chart.

Column C data "2021-02-08T19:03:53.3989786-06:00" can be broken down as:
2021-02-08 (Plan run Date)
T (can be ignored)
19:03:53 (Time as RunEnd, the rest of the string for time can be ignored, "3989786-06:00")

Column D has been converted to H:M:S (In these examples, they are all M:S).

In the chart example, the first instance should have a label of "2021-02-08 19:03:53", preferably in the Y axis stacked.
(Assume rotated 90 degrees)
2021-02-08
19:03:53
(See attached image)

I am able to step thru rows of Column A, but I don't know what to do to collect the data based on the PlanName and not mix data.

I'm not looking for solutions, Id like to make this a learning experience if some one is willing to help me along on what I should do, read, attempt, etc...

Before I embark on the VB code to make this automatic, I have a couple observations / questions.

First of all, it's a line chart you want.

Here is my plan of attack: parse out the date-time using the formulas in Columns H:J.

On the Pivot Sheet, I have two pivot tables: The one in column A has a list of unique plans. A pivot table is a cheap and quick way to get a unique list of values.

The one in columns C&D has one plan.

What I will do is use VBA to run what I call a "pointer" down the first pivot table and use it to set the filter in Cell D1. The results of this pivot table will be overlaid with named dynamic ranges that will be used to generate the chart.

I will then create a sheet with the name of the plan and copy the chart to it. The question I have is, do you want this chart to be dynamic or can it be just a static picture of the chart? The later is a lot easier to accomplish.

Originally Posted by dflak

Before I embark on the VB code to make this automatic, I have a couple observations / questions.

1. First of all, it's a line chart you want.

Here is my plan of attack: parse out the date-time using the formulas in Columns H:J.

On the Pivot Sheet, I have two pivot tables: The one in column A has a list of unique plans. A pivot table is a cheap and quick way to get a unique list of values.

The one in columns C&D has one plan.

What I will do is use VBA to run what I call a "pointer" down the first pivot table and use it to set the filter in Cell D1. The results of this pivot table will be overlaid with named dynamic ranges that will be used to generate the chart.

2. I will then create a sheet with the name of the plan and copy the chart to it. The question I have is, do you want this chart to be dynamic or can it be just a static picture of the chart? The later is a lot easier to accomplish.

Thank you for your time and assistance!!

1. Line Chart, got it!
2. I think a static picture would be sufficient for individual sheets by PlanName.
The Data worksheet will be an accumulation over a 1 year period. It will end up with a lot of rows of data the same as the initial excel file I provided, and will create charts where a static picture should be fine as I generate weekly/monthly reports.
Can I just change the width of the chart before it creates a static picture?

Note:
I noticed as I changed the width the X axis labels went to Zero degrees.

We have an issue. Sheet names can be a maximum of 31 characters. Some of your plan names are longer than this. There are two ways I can see around this.

• Come up with a scheme to abbreviate the names
• Create a navigation table that has the Plan Name and an alias sheet name. In other words, when you click on the plan name, it will take you to the sheet number that has that plan on it

I think it is safest to go with plan B: Make a navigation table. It complicates things a bit, but if you are willing to accept one of my modules "on faith," it will be OK. It's a piece of code that you may want to save for a future project. I'll explain how to export and import modules.

As far as the chart sizes go, whatever size the chart is on the main screen, that's the size the chart will be everywhere else.

Here is the file and an explanation how I did some of the stuff.

Originally Posted by dflak

We have an issue. Sheet names can be a maximum of 31 characters. Some of your plan names are longer than this. There are two ways I can see around this.

• [1]Come up with a scheme to abbreviate the names
  [2]Create a navigation table that has the Plan Name and an alias sheet name. In other words, when you click on the plan name, it will take you to the sheet number that has that plan on it

I think it is safest to go with plan B: Make a navigation table. It complicates things a bit, but if you are willing to accept one of my modules "on faith," it will be OK. It's a piece of code that you may want to save for a future project. I'll explain how to export and import modules.

As far as the chart sizes go, whatever size the chart is on the main screen, that's the size the chart will be everywhere else.

1. Sheet names can be simplified.
Example: 0250018_REV_E_1
Can be changed to "0250018"
Trim all characters after the first "underscore" on any of the names for PlanName. This is a standard format we use.

2. What?
I'm following a bit of what your explaining, its the formulas and Pivot tables that I don't understand, just never used them.

If Plan B is what your latest excel file is doing, that works. The only thing is the sheet names.

Side note:
I tried the latest workbook on live data.
It only included the first few entries as available in the original workbook.
Live data right now is 2,464 rows.

I'll work with the shortened names. There version I sent you has the sheet names aliases and you can go to the alias by double clicking on the plan name or aliases.

I can remove that now.

As for the formulas:
=DATE(LEFT([@RunEnd],4),MID([@RunEnd],6,2),MID([@RunEnd],9,2))

The date function creates a date based on =Date(Year,Month,Day) - the Left and Mid parse out the year, month and date from the RunEnd string.

=TIMEVALUE(MID([@RunEnd],12,8))

Parsing out the time is a bit easier since it is all contiguous and in the format of a date. It is still a string which is why we had to wrap it in TIMEVALUE.

To get an abbreviated name, I use: =LEFT([@PlanName],FIND("_",[@PlanName])-1) - the Excel Sheet Find function returns the string position where it finds whatever it is you are looking for. So I look for the underscore using find. That gives me the string position when it is found and I want everything left of that.

With the abbreviated data you gave me, the last of the plans abbreviated name is simply FLS.

As for the pivot tables. I will hold off. One of my assumed duties at the company at which I work is to teach Excel to other team members. The next lesson is pivot tables. When I develop it, I will present it to you.

I got the date - time by adding the date and time.

In Excel dates are numbers. Days are whole days since Jan 1, 1900 and time is fractional days. For example, 6 AM is .25 days, etc.

I will have the next version of this report to you sometime between tonight and the weekend .

I cant express how much I appreciate the effort and time you have given towards my project, not to mention how well you have explained how and why.
I am very grateful!