Microsoft Excel - Scatter Plot with Multiple Series either Vba Code or help how to

hi i am trying to create a scatter plot with multiple series on them i have probalby over 200 Customer in a list which changes when you you select a product so some can #N/A Vlaues in the range.

I am trying to create a scatter which uses the Customer name as a series and then plots the X and Y Data, at the moment i can only do it manually i am wondering if there is a way of vba coding to create the scatter graph for a particular range rather than manually adding x number of series and selecting the x and y data.

any help would be greatly appreciated 

Name	Xdata	Ydata
Customer A	£30,075	£2.76
Customer B	£25,920	£2.64
Customer C	£14,828	£3.36
Customer D	£9,310	£2.95

#N/A                                        #N/A      #N/A

Thanks

Hello Paul
This is very basic to plot your points and label each point with the customer name.  It is only set to work with the data range that you have given(Rows 2 to 6).  It uses A1 for the chart title and B1 and C1 for the axis labels.  The chart is generated as a chart sheet.  It will need some work to dynamically adjust to different data ranges but without knowledge of how you are gathering your data it is hard to be more general.

Sub AddChartSheet()
   'Dimension variables.
   Dim Counter As Integer, ChartName As String, xVals As String

   Dim chtChart As Chart
   
   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False
   
   'Create a new chart.
   Set chtChart = Charts.Add
   With chtChart
    .Name = "CustomerSales"
    .ChartType = xlXYScatter
    'Link to the source data range.
    .SetSourceData Source:=Sheets("Sheet1").Range("B2:C6"), _
     PlotBy:=xlColumns
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "=Sheet1!A1"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Caption = "=Sheet1!B1"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Caption = "=Sheet1!C1"
   End With
    
   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(1).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
    Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
    xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
   ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
     True
    ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
     Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
   Next Counter

End Sub

Regards
Harry

Hi Harry

Thanks for replying, the data is feeding off a pivot table, so when a selection is made cells to the right of the pivot table updates if error or blank it will show #N/A the range will be R64:T1636, when i have used your code the scatter plots Revenue on Y Axis Rather than X Axis, and price vice versa also it plots them as a seperate series. i have attached an example of the scatter plot i would like, your help is greatly appreciated. its so when you hover over a particular dot it will give you customer name, revenue and price, if it helps the series dont have to have different symbols they can be all the same for each customer/series.

Thanks

Hello Paul,
If the data plots on different axes then it must be in different column order to that shown in your example data in the original post.  In my test it plots only as one series so there must be something fundamentally different about your data.
Are you really plotting nearly 1600 points on the one chart?
There was no example attached but a sample spreadsheet (sanitised and small database size) would help.
I am going to be away for a few days and might not be able to get back to you in short order.
Regards
Harry

Hi Harry

I have attached an example of what i am working with, so when i fitler on a different product it will update the pivot and then update the scatter. also on the chart title i would like it to show as the product selected so cell B1 in the example, I would like the graph to plot a series if it has a name and values so it would exclude any with #N/A 
ThanksScatter Plot Example.zip

Hi Paul,
In your sample chart you only plot 5 points as individual points rather than a series.
Is that what you want to do and what decides the five points that you plot?  If not, what exactly do you want to plot and how?
Do you want the legend and does it have to be at the bottom? 
How are you going to distinguish the different customer points or does that not matter?
Regards
Harry

Hi harry

Apologies for onyl just replying it doesnt really matter about the elgedn being visible, i also dont need to distinguish the customer points aslong as the name appears when you select that point. what i am looking for is to plot all points that have a values from the filter on to the chart i only plotted the first 5, to do them all would have been too time consuming. it is what ever is easiest it can be a series or individual points aslong as when you hover over that particluar point it will show the name, price and revenue

Thanks 

Paul,
If you plot as many points as you have in your categories, they will be so crowded as to be unintelligible.  It might be possible to set up a chart with a limited number of points visible at one time and a slider to allow you to scroll through the values.
I seem to recall that I have done something like this in the past.  Do you think that would meet your requirements?
Regards
Harry

Hello Paul,
Here is an implementation of the system that I talked about.  The chart shows ten values.  The slider bar will advance you through the data set. Clicking once on the scroll bar arrow advances to the next ten values.  Clicking between the slider and the arrow once will advance one hundred values.  There are two small macros to reset the range of the slider when the selection is changed.

Paul_Example.zip

Hope this helps.
Regards
Harry

Hi harry

Thank you for sending the example through i definitely can use this for something else but for this particular graph requirement i would need to to show all the values, is this possible,

Thanks

Hello Paul,
Not really in the format that you want.  This is because there is a maximum of 255 series on a chart and the only way to get what you want is to have each point as a separate series.
If you put them in as a single series, you do not get the individual business identification when you hover over the point.  Also if you have over a thousand points (or even 255) on the chart the points are so congested that you probably can't distinguish one from another so the chart would be virtually unusable in the way that you want to use it.  You can see for yourself what it would look like just by making a chart from the full data set.
If you can explain in more detail exactly why you want to do what you want to do then it might be possible to suggest some alternatives.
Regards
Harry