SOLVED

Creating a larger table from input data

Copper Contributor

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.

excel problem.PNG

5 Replies
best response confirmed by CHende77 (Copper Contributor)
Solution

@CHende77 

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

 

@CHende77 

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

@CHende77 

If you are office365 user, you may carry out as per below. 

There will be 5 steps to get the result you want.

 

Starrysky1988_0-1648483217897.png

 

Thank you so much!
Thank you, very nice approach to this problem!
1 best response

Accepted Solutions
best response confirmed by CHende77 (Copper Contributor)
Solution

@CHende77 

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

 

View solution in original post