How to make YX scatter instead of XY scatter Excel Graph

[charismawelly]

Hi guys,

I need help. I have data such as:

Y X1 X2 X3
1 45 56 71
2 22 33 88
3 44 22 99
4 17 88 19
5 88 44 15
6 66 22 12

If I use XY scatter plot by selecting that all data, I will got plot data like such as 3 horizontal line because excel read 1-6 as X instead as Y.
That I expect is to get 3 vertical line. I can do it manually but I have a lot of data that deal with this problem. It might save my time a lot.
Please share if you have any idea about this. I am really appreciate your help. Thank you.

Regards,
Welly

 

[oldbrewer]

Take this data:
Y X1 X2 X3
1 45 56 71
2 22 33 88					in the table below the spaces and number lengths are determined by formulas
3 44 22 99
4 17 88 19
5 88 44 15								Y	X1	X2	X3
6 66 22 12				Y X1 X2 X3	1st space	2nd space	3rd space	num1	num2	num3	num4
					1 45 56 71	2	5	8	1	45	56	71
And change it to this:			2 22 33 88	2	5	8	2	22	33	88
X1 Y					3 44 22 99	2	5	8	3	44	22	99
45 1					4 17 105 19	2	5	9	4	17	105	19
22 2					5 113 44 15	2	6	9	5	113	44	15
44 3					6 66 22 12	2	5	8	6	66	22	12
17 4
88 5
66 6
X2 Y					X1	Y			this very simple macro pulls the required data
56 1					45	1			from the tableabove
33 2					22	2
22 3					44	3
88 4					17	4
44 5					113	5				x = 19
22 6					66	6				For j = 9 To 14
										x = x + 1
X3 Y										Cells(x, 7) = Cells(j, 10)
71 1										Cells(x, 6) = Cells(j, 11)
88 2					X2	Y				Next j
99 3					56	1				x = 29
19 4					33	2				For j = 9 To 14
15 5					22	3				x = x + 1
12 6					105	4				Cells(x, 7) = Cells(j, 10)
					44	5				Cells(x, 6) = Cells(j, 12)
					22	6				Next j
										x = 39
										For j = 9 To 14
										x = x + 1
					X3	Y				Cells(x, 7) = Cells(j, 10)
					71	1				Cells(x, 6) = Cells(j, 13)
					88	2				Next j
					99	3				End Sub
					19	4
					15	5
					12	6

<colgroup><col span="5"><col><col span="10"></colgroup><tbody>
</tbody>

 

[oldbrewer]

before someone jumps n here is a shorter version of the macro

d = 10
For x = 11 To 31 Step 10
d = d + 1
For j = 9 To 14
xx = x + j + 2
Cells(xx, 7) = Cells(j, 10)
Cells(xx, 6) = Cells(j, d)
Next j
Next x
End Sub

 

[charismawelly]

Well, you could write a program to do this. I did that for myself a long time ago, to accept various input data arrangements other than Excel's default X-Y1-Y2-Y3 to create scatter plots. Y-X1-X2-X3 is one of the options (not limited to 3), as are X1-Y1-X2-Y2-X3-Y3, Y1-X1-Y2-X3-Y3-X3, X1-X2-X3-Y1-Y2-Y3, etc. All I have to do is select the data range, indicate the data layout, and click OK.

I have made this program part of a commercial software package, the Peltier Tech Chart Utility, which you can find using links on my blog. I normally do not mention this product here, because I don't want to take advantage of the forum to spam other members. If anyone complains, or if this reply violates one of the forum rules, then this reply can be deleted.

Ah.. thank you for you advise.. it might take more time for me to write programs.. hahaha..
I have check your commercial package but am looking for the free solution :D
Am not sure this issue will have a code solution in available online source code.

 

[charismawelly]

before someone jumps n here is a shorter version of the macro

d = 10
For x = 11 To 31 Step 10
d = d + 1
For j = 9 To 14
xx = x + j + 2
Cells(xx, 7) = Cells(j, 10)
Cells(xx, 6) = Cells(j, d)
Next j
Next x
End Sub

Thanks guys for the advise. I'll be back after I try to apply it.

 

[Jon Peltier]

Note that oldbrewer's approach will copy worksheet data into additional ranges, which you will then have to make into charts. And the new ranges aren't linked to the original data, though that's an easy adjustment.

To create a chart from your existing data you'll need a different approach. I wrote a tutorial long ago called Excel XY Chart Variations with VBA. It covered a few nonstandard data layouts, but not yours.

We'll assume data like this, with Y in the first column and X(1) to X(N) in the next N columns, and series names in the first row. The actual numbers of rows and columns are accounted for in the program.

Y X1 X2 X3
1 45 56 71
2 22 33 88
3 44 22 99
4 17 88 19
5 88 44 15
6 66 22 12

Here's the code:

Sub OneY_MultiX_Chart()

  Dim rngDataSource As Range
  Dim iDataRowsCt As Long
  Dim iDataColsCt As Integer
  Dim iSrsIx As Integer
  Dim chtChart As Chart
  Dim srsNew As Series

  If Not TypeName(Selection) = "Range" Then
    '' Doesn't work if no range is selected
    MsgBox "Please select a data range and try again.", _
        vbExclamation, "No Range Selected"
  Else
    Set rngDataSource = Selection
    With rngDataSource
      iDataRowsCt = .Rows.Count
      iDataColsCt = .Columns.Count
    End With

    '' Create the chart
    ActiveSheet.Shapes.AddChart.Select
    Set chtChart = ActiveChart
    Application.GoTo rngDataSource

    With chtChart
      .ChartType = xlXYScatterLines

      '' Remove any series created with the chart
      Do Until .SeriesCollection.Count = 0
        .SeriesCollection(1).Delete
      Loop

      For iSrsIx = 1 To iDataColsCt - 1
        '' Add each series
        Set srsNew = .SeriesCollection.NewSeries
        With srsNew
          .Name = rngDataSource.Cells(1, 1 + iSrsIx)
          .Values = rngDataSource.Cells(2, 1) _
              .Resize(iDataRowsCt - 1, 1)
          .XValues = rngDataSource.Cells(2, 1 + iSrsIx) _
              .Resize(iDataRowsCt - 1, 1)
        End With
      Next

      .ClearToMatchStyle
      Select Case Val(Application.Version)
        Case 12, 14
          .ChartStyle = 2
        Case 15
          .ChartStyle = 240
      End Select
    End With
  End If
End Sub

Just select the range and run the code.