Forum Discussion
Creating a larger table from input data
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.
Here 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
5 Replies
If you are office365 user, you may carry out as per below.
There will be 5 steps to get the result you want.
- Thank you, very nice approach to this problem!
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 SubHere 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- Thank you so much!
