define chart series from single column

[Guest]

Hi list,
is there any way to define different series for a scatterplot from a single
column?
I have data organised in three columns: column 1 and 2 contain the X and Y
coordinates and column 3 contains the codes (to define the series).
X Y code
-0,25586 -1,41622 A
0,00779 -1,27963 A
0,80316 -0,56037 A
0,72824 0,40388 A
0,89184 0,53479 A
-0,44546 -0,11075 B
-0,28927 -0,16976 B
-0,02147 0,18616 B
0,01244 0,63664 B
0,01123 0,66995 B

Normally you put the series next to each other, but that is very time
consuming if you have a lot of series. It would be easier if Excel could
recognize the labels in column 3 as separate series.

Maarten

 

[Guest]

Her is some code that will help. On sheet1 put the x,y,code in columns a - c
starting at row 1. then run macro.



Sub Scatterplot()


First = True
Endrow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = 1
Do While RowCount <= Endrow

Firstrow = RowCount
Sheets("sheet1").Cells(RowCount, 1).Activate
Do While Cells(RowCount, "C") = Cells(RowCount + 1, "C")
RowCount = RowCount + 1
Loop
Lastrow = RowCount

If First = True Then

Set ChartRange = Sheets("Sheet1").Range("A" & CStr(Firstrow) & ":B" &
CStr(Lastrow))
Charts.Add
newchartname = "Chart " & CStr(Mid(ActiveChart.Name, 6))
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=ChartRange
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
newchartname = Mid(ActiveChart.Name, 6)
newchartname = Mid(newchartname, InStr(newchartname, "Chart"))
First = False
Else

Worksheets("sheet1").ChartObjects(newchartname).Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = _
"=Sheet1!R" & CStr(Firstrow) & "C1:R" & CStr(Lastrow) & "C1"
ActiveChart.SeriesCollection(2).Values = _
"=Sheet1!R" & CStr(Firstrow) & "C2:R" & CStr(Lastrow) & "C2"
End If

RowCount = RowCount + 1
Loop
End Sub

 

[Guest]

Works great! Thanks!

Her is some code that will help. On sheet1 put the x,y,code in columns a - c
starting at row 1. then run macro.



Sub Scatterplot()


First = True
Endrow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = 1
Do While RowCount <= Endrow

Firstrow = RowCount
Sheets("sheet1").Cells(RowCount, 1).Activate
Do While Cells(RowCount, "C") = Cells(RowCount + 1, "C")
RowCount = RowCount + 1
Loop
Lastrow = RowCount

If First = True Then

Set ChartRange = Sheets("Sheet1").Range("A" & CStr(Firstrow) & ":B" &
CStr(Lastrow))
Charts.Add
newchartname = "Chart " & CStr(Mid(ActiveChart.Name, 6))
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=ChartRange
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
newchartname = Mid(ActiveChart.Name, 6)
newchartname = Mid(newchartname, InStr(newchartname, "Chart"))
First = False
Else

Worksheets("sheet1").ChartObjects(newchartname).Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = _
"=Sheet1!R" & CStr(Firstrow) & "C1:R" & CStr(Lastrow) & "C1"
ActiveChart.SeriesCollection(2).Values = _
"=Sheet1!R" & CStr(Firstrow) & "C2:R" & CStr(Lastrow) & "C2"
End If

RowCount = RowCount + 1
Loop
End Sub