Mar 28 2022 06:26 AM
Hello experts,
I am using excel (version 2202) on a PC with windows 10. I have a situation where I need to input x and y coordinates in a table and automatically receive a larger list of coordinates from it. Whenever the y value differs from the one above it in the table, I need two points to be created for it. When graphed, this will created a vertical "step" whenever the y values change. I am trying to find a way to do this automatically for any input since future data will not have the same values or steps in the same places. I've attempted to use IF statements and the such with no success. I've posted a picture of an example that I did by hand. Thank you in advance for your time.
Mar 28 2022 06:46 AM - edited Mar 28 2022 07:01 AM
SolutionHere is a macro that will create the output range:
Sub CreateOutput()
Dim s As Long
Dim t As Long
Dim m As Long
Application.ScreenUpdating = False
Range("D3:E" & Rows.Count).ClearContents
m = Range("A" & Rows.Count).End(xlUp).Row
s = 3
t = 3
For s = 3 To m
If s > 3 And Range("B" & s).Value <> Range("B" & s - 1).Value Then
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s - 1).Value
t = t + 1
End If
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s).Value
t = t + 1
Next s
Application.ScreenUpdating = True
End Sub
Mar 28 2022 07:03 AM
This version will also create the chart:
Sub CreateOutput()
Dim s As Long
Dim t As Long
Dim m As Long
Application.ScreenUpdating = False
Range("D3:E" & Rows.Count).ClearContents
m = Range("A" & Rows.Count).End(xlUp).Row
s = 3
t = 3
For s = 3 To m
If s > 3 And Range("B" & s).Value <> Range("B" & s - 1).Value Then
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s - 1).Value
t = t + 1
End If
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s).Value
t = t + 1
Next s
With ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Chart
.SetSourceData Source:=Range("D2:E" & t - 1)
.HasTitle = False
End With
Application.ScreenUpdating = True
End Sub
Mar 28 2022 09:01 AM - edited Mar 28 2022 09:03 AM
If you are office365 user, you may carry out as per below.
There will be 5 steps to get the result you want.
Mar 28 2022 09:20 AM
Mar 28 2022 06:46 AM - edited Mar 28 2022 07:01 AM
SolutionHere is a macro that will create the output range:
Sub CreateOutput()
Dim s As Long
Dim t As Long
Dim m As Long
Application.ScreenUpdating = False
Range("D3:E" & Rows.Count).ClearContents
m = Range("A" & Rows.Count).End(xlUp).Row
s = 3
t = 3
For s = 3 To m
If s > 3 And Range("B" & s).Value <> Range("B" & s - 1).Value Then
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s - 1).Value
t = t + 1
End If
Range("D" & t).Value = Range("A" & s).Value
Range("E" & t).Value = Range("B" & s).Value
t = t + 1
Next s
Application.ScreenUpdating = True
End Sub