automate the creation of a XY Scatter chart

[Harry Covair]

Hi the forum,

Since yesterday, I try to solve a problem that combines technical VBA knowledge and programming logic.

Context (see attachment)
On a spreadsheet, I have 5 information: Prod, Position, Name, ID and Team.
The total number of lines, the number of vendors and the number of lines by vendors vary.

The goal
Write a VBA procedure that
• Creates an XY scatter (straight lines and markers)
• Adds a series by vendor
• Adds a linear trendline by vendor
• (if possible giving the trendline the same color as the corresponding curve)
• Provide a title (= team) to the graph.

What I have done :
A routine that looks for breaks on vendors
Record a macro that creates an XY scatter (straight lines and markers)

My problem(s)
Integrating these two routines to add the XY scatter

Should a member of this forum have an example that I would solve such problem?

As attachment
· A file with the "results" sheet that shows the table and an example of the chart I want (manually created).
· In the VBE, both procedures.

Are more information required, please ask.

Thanks in advance

Harry

 

[Luke M]

Hi Harry,

Before going too far, I might ask if the end result on this path would actually be helpful. With the data you gave, chart looks like:


Good luck figuring out what that means!

Instead, I would suggest an alternative:
Let's plan on the chart showing up to 3 different vendors at a time. These can be chosen by some sort of control, perhaps a data validation drop box.
We use a PivotTable that looks at col A:E of your source data, which quickly compiles all the information and arranges it into a nice layout for us. Then, we can build out chart how we like and give something that should be more useful

 

[Marc L]

Luke, your first chart is impressive !

May the force still be with you …​

 

[Hui]

When displaying trend lines I prefer to show the data as Markers only
So that the chart is less cluttered
I also think markers show the distribution better than using the connecting lines
Using Lukes data I would display as:

 

[NARAYANK991]

Hi ,

I would prefer to use lines ( with or without markers ) when the number of points are few , since connecting dots ( markers ) which are spread out , especially when there are many with different colours / shapes , is more difficult.

When the number of dots are many and clustered together , then I suggest that we drop the lines and have only the markers.

Trendlines are treated differently , since trendlines are not supposed to have too many ups and downs ; if trendlines look like the graphs themselves , they don't really add value.

Narayan

 

[Harry Covair]

Hi Luc M,

Hello to the other stakeholders

When I read your solution, two words came to my mind: congratulation and thank you.

It is also a beautiful application of the adage before thinking VBA, think Excel

Your solution is not only original but she also proves an in-depth knowledge of Excel. Furthermore, she is clever. It is exactly what I was looking for.

I did bring only one change by replacing the combination Index-Match in the Name Manager by the Offset function. I have more affinity with Offset that with Index-Match.

One point brings me in the trouble: how did you succeed in sorting alphanumeric data in the pivot-table?

Once a more, many thanks.

Have a nice day

Harry.

 

[Luke M]

Hi Harry,

Thank you for the kind words. That kind of thanks is the reason most of us continue to hang around and help out in forums.

For your questions...
Usually, when you add a field to a PivotTable, it tends to automatically sort things. If not, you can choose Sort from the dropdown


I will note that for the ET dates, I had to manually arrange them, as sorting alphabetically would have made the list
ET1,ET10,ET11,...ET17,ET2
so I had to drag-and-drop the ET2-ET8 into correct position.

For using OFFSET, I'd agree, go with what your familiar with. OFFSET has a slight negative in that it's a volatile function (get's recalculated every time a change is made in workbook), but as long as you don't have too many in the workbook, you won't notice a thing.

Per Hui's and Narayan's comments, give some thought if you should use just markers or lines. I think if the point of the graph is to show the trendlines, use the markers in Hui's. If you want to instead focus on original data, leave it as is.

 

[Harry Covair]

Thank you for the clarification.

During the weekend, I somewhat improved the application based on the philosophy of your solution.

I did not consider the possibility of drop-down lists to choose the participants because the pivotchart has a choice function.

However I’m facing with another problem for which I initiate a new thread in this forum.
Harry