Excel Split Single Column Into Multiple Columns Based on Header Rows

Question

I have an excel file that contains data exported from LTSpice simulations. There are 280 different runs however the data is exported as two columns (time and voltage) with a run cell at the start of a new run. The number of data points in each run varies. Looks something like this:

Run 1/280
Time1        Voltage1
Time2        Voltage2
Run 2/280
Time1        Voltage1
Time2        Voltage2
Time3        Voltage3
Run 3/280

I would like to have the run cells as row and the time and voltage columns beneath them.

Run 1/280                Run 2/280                Run 3/280
Time1        Voltage1    Time1        Voltage1
Time2        Voltage2    Time2        Voltage2
                         Time3        Voltage3

I haven't found an easy way to do this yet, so any help would be appreciated.

Thanks

Answer 1

Without VBA...

For each row of your input list, you need to identify its type (Run x/xxx header or terminal, voltage pair) and the row and pair of columns in the output where this input row belongs.

In the picture below, columns A and B perform this task. Column A identifies the output column pair and B the output row, where row 0 indicates the header row of the output.

The header row of the output utilises the fact that if an array is sorted in ascending order and has repeated values then MATCH(x,array,0) finds the index of the first element in array equal to x. The cumbersome repetition of the SUMPRODUCT term in the formulae for the other rows is necesssary for the following reason. If there is no matching pair in columns A and B to the current output row and column pair number then the SUMPRODUCT delivers 0 and, unfortunately, the INDEX(array,SUMPRODUCT()) term evaluates to INDEX(array,0) which delivers the first element of array (*) - which is not what is wanted.

You obviously need sufficient helper values in row 1 and column E of the worksheet in the output area - the maximum values of columns B and A, respectively determine the requirements. Oversizing the output (as the picture has done) is not a problem as the formulae in any redundant positions simply evaluate to "".

(*) In fact, for a single column array the formula =INDEX(array,0) evaluates to the array itself. When used as cell formula (rather than being used as an array formula across a range of cells) formula simply picks the first value from array.

Answer 2

Please try this code.

Sub SplitToColumns()
    ' 16 Sep 2017

    Dim WsS As Worksheet                    ' S = "Source"
    Dim WsD As Worksheet                    ' D = "Destination"
    Dim WsDName As String
    Dim RunId As String                     ' first word in "Run 1/280"
    Dim RowId As Variant                    ' value in WsS.Column(A)
    Dim Rl As Long                          ' last row (WsS)
    Dim Rs As Long, Rd As Long              ' row numbers
    Dim Cd As Long                          ' column (WsD)

    WsDName = "RemoteMan"                   ' change to a valid tab name
    Application.ScreenUpdating = False
    On Error Resume Next
    Set WsD = Worksheets(WsDName)
    If Err Then
        ' create WsD if it doesn't exist:
        Set WsD = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        WsD.Name = WsDName
        Cd = -1
    Else
        ' continue adding new data to the right of existing,
        With WsD.UsedRange
            Cd = .Columns.Count - 1
            If Cd = 1 And .Rows.Count = 1 Then Cd = -1
        End With
    End If

    Set WsS = Worksheets("Remote")          ' change to a valid tab name
    With WsS
        ' presume "Run" & Time in column A, Voltage in Column B
        ' presume: no blank rows
        Rl = .Cells(Rows.Count, "A").End(xlUp).Row
        RunId = .Cells(2, 1).Value          ' row 2 must have the RunId
        RunId = Left(RunId, InStr(RunId, " ") - 1)
        For Rs = 2 To Rl                    ' assume data start in row 2 (A1 may not be blank!)
            RowId = .Cells(Rs, "A").Value
            If InStr(1, RowId, RunId, vbTextCompare) = 1 Then
                Rd = 1                      ' first row to use in WsD
                Cd = Cd + 2                 ' determine next columns
            End If
            WsD.Cells(Rd, Cd).Value = RowId
            WsD.Cells(Rd, Cd + 1).Value = .Cells(Rs, "B").Value
            Rd = Rd + 1                     ' next row to use
        Next Rs
    End With
    Application.ScreenUpdating = True
End Sub