Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
define chart series from single column
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
define chart series from single column
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 Whe=xlLocationAsObject, Name:="Sheet1" newchartname = Mid(ActiveChart.Name, 6) newchartname = Mid(newchartname, InStr(newchartname, "Chart")) First = False Else Worksheets("sheet1").ChartObjects(newchartname).Ac tivate 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 "Maarten" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
define chart series from single column
Works great! Thanks!
"Joel" wrote: 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 Whe=xlLocationAsObject, Name:="Sheet1" newchartname = Mid(ActiveChart.Name, 6) newchartname = Mid(newchartname, InStr(newchartname, "Chart")) First = False Else Worksheets("sheet1").ChartObjects(newchartname).Ac tivate 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 "Maarten" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
define series in scatterplot from column | Charts and Charting in Excel | |||
how do I define a chart series with an indirect reference | Charts and Charting in Excel | |||
Displaying single and multiple data series.Single data series | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |