Scatterplot with multiple series, without specifying the ranges by hand

[yzapp]

Hi!
I have a problem when trying to create a scatter plot with mutiple series.
I have three columns; one with the series name, one with the x values and one with y values.

Example:
Series name,,,,,, x-values,,,,,,, y-values
803b,,,,,,,,,,,,,,,,,,5 ,,,,,,,,,,,,,,,,,,,6
803b,,,,,,,,,,,,,,,,,,9,,,,,,,,,,,,,,,,,,, 4
803b,,,,,,,,,,,,,,,,,,1 ,,,,,,,,,,,,,,,,,,,8
128q,,,,,,,,,,,,,,,,,10,,,,,,,,,,,,,,,,,,,96
128q,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,,,,,,,,,8
719,,,,,,,,,,,,,,,,,,,,45,,,,,,,,,,,,,,,,,75
719,,,,,,,,,,,,,,,,,,,,60,,,,,,,,,,,,,,,,,,9
512,,,,,,,,,,,,,,,,,,,,6,,,,,,,,,,,,,,,,,,,,4

I know that i could easily define each series manually. This however would take ages since there are 50+ different series names and different amounts of x- and y-values.
So i want excel to do this for me.
Any suggestions how to solve this is greatly appreciated?

PS
Sorry for the ugly looking example, forum deleted my spaces
DS

//yzapp

 

[p45cal]

Have each of your 50+ series only got one point each?
I'd gues you might want a single series with the labels from the first column?

 

[yzapp]

No, the series have different amounts of data points ranging from 1 to about 20.
The point is that i want to be able to see which points that corresponds to a specific name (the series names in the example) in the scatter plot. I thought by doing several series will create different colors and in that way be able to seperate the massive amount of data points.

So to answer your second question, yes that would work. As long as i can see which data that corresponds to which name.

 

[p45cal]

Have a look here: http://www.appspro.com/Utilities/ChartLabeler.htm

Otherwise here's a bit of code to get you started (it adjusts the text of existing labels of the first (and only, preferably) series, so make sure labels have been added before you run it. Also, make sure the chart is selected before you run it):

Sub blah()
rw = 2
For Each pt In ActiveChart.SeriesCollection(1).Points
    pt.DataLabel.Text = Cells(rw, "A").Value
    rw = rw + 1
Next pt
End Sub

<embed src="http://www.box.net/embed/nnjhhglmcmrxdub.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" width="466" height="400">

 

[yzapp]

The program you suggested would probably work, but i can't install it since i do not have administrator properties.
Regarding the code you provided, i inserted it into a macro file and ran it. I then got an error with something like this, The method 'DataLabel' in the object 'Point' failed.
I will be back again tomorrow morning checking for your advise, thanks so far!

 

[p45cal]

The program you suggested would probably work, but i can't install it since i do not have administrator properties.
Regarding the code you provided, i inserted it into a macro file and ran it. I then got an error with something like this, The method 'DataLabel' in the object 'Point' failed.
I will be back again tomorrow morning checking for your advise, thanks so far!

maybe because you missed "so make sure labels have been added before you run it" or perhaps you have labels, but not one for every point - you may have deleted one or two - in which case delete labels for the series and add them again.

 

[yzapp]

Yes I think I got ahead of myself there, and im a total newbie to macros.
The code just causes excel to crash, maybe there are to many data points?
Does the macro edit the labels to the names in the first column?
If it does then it will be to hard to see all the points.

 

[p45cal]

Can you post a file somewhere on the internet (box.net?) and provide a link to it?
The file should contain the data you want to plot and a chart as you would like to be created automatically, say with 4 or 5 series done manually. If the data is confidential/sensitive, alter it or send the link to me by Private Messaging here.

 

[yzapp]

Just remembered that i have an account at box. I made an example:
http://www.box.net/shared/8y6g14tjxrpmbup9mjd3

The data is not sensitive but i altered it anyway.
The important thing is not that there are different series, but that I can recognize easily which data that corresponds too a particular "series name". I don't even have to know the names of the "Series name" in the plot but i need them to look different. Though the earlier would be more easy for me.
Hope this makes any sense to you

 

[p45cal]

see file here.

Code:

Sub blah()
LR = Cells(Rows.Count, 1).End(xlUp).Row
rw = 2
Set NewCht = ActiveSheet.Shapes.AddChart(xlXYScatter, 16, 16, 512, 512)
With NewCht.Chart
    Do Until .SeriesCollection.Count = 0
        .SeriesCollection(1).Delete
    Loop
End With
Do Until rw >= LR
    If Cells(rw, 1) <> Cells(rw - 1, 1) Then
        StartRow = rw
        Do
            rw = rw + 1
        Loop Until Cells(rw, 1) <> Cells(rw - 1, 1)
        Endrow = rw - 1
        '       Stop
        With Range(Cells(StartRow, 1), Cells(Endrow, 1))
            Set SeriesNameRng = .Cells(1)
            Set xValsRng = .Offset(, 1)
            Set yValsRng = .Offset(, 2)
            With NewCht.Chart.SeriesCollection.NewSeries
                '.Name = SeriesNameRng 'removed
                .Name = "=" & SeriesNameRng.Address(external:=True) 'added
                .Values = yValsRng
                .XValues = xValsRng
            End With
        End With
    End If
Loop
End Sub

You'll have to make the chart bigger in order to see the complete legend.

I've made a small change to the code (see comments in the code) and will update the linked file in a sec.