Transpose onto separate lines

[Kelli Webb]

Hi, As per the attached I would love assistance to have a separate line for each Course name in column B and respective course status in column C as per the below example.

Thanking you in advance - Kelli!

 

[shrivallabha]

See if this code helps your case. It should read from column A to C and will output results on F to H.

Public Sub SplitData()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim varOutB, varOutC

Application.ScreenUpdating = False
ws.Range("F:G").ClearContents
For i = 2 To ws.Range("C" & ws.Rows.Count).End(xlUp).Row
    varOutB = Split(ws.Range("B" & i).Value, Chr(10))
    varOutC = Split(ws.Range("C" & i).Value, Chr(10))
    If UBound(varOutB) > 1 Then
        If UBound(varOutB) <> UBound(varOutC) Then
            ws.Range("D" & i).Value = "Unable to split!"
        Else
            For j = LBound(varOutB) To UBound(varOutB)
                k = ws.Range("F" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
                ws.Range("F" & k).Value = ws.Range("A" & i).Value
                ws.Range("G" & k).Value = varOutB(j)
                ws.Range("H" & k).Value = varOutC(j)
            Next j
            ws.Range("D" & i).Value = "Split Successfully!"
        End If
    Else
        k = ws.Range("F" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
        ws.Range("F" & k).Value = ws.Range("A" & i).Value
        ws.Range("G" & k).Value = ws.Range("B" & i).Value
        ws.Range("H" & k).Value = ws.Range("C" & i).Value
        ws.Range("D" & i).Value = "No split required!"
    End If
Next
Application.ScreenUpdating = True

End Sub

 

[Kelli Webb]

See if this code helps your case. It should read from column A to C and will output results on F to H.

Public Sub SplitData()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim varOutB, varOutC

Application.ScreenUpdating = False
ws.Range("F:G").ClearContents
For i = 2 To ws.Range("C" & ws.Rows.Count).End(xlUp).Row
    varOutB = Split(ws.Range("B" & i).Value, Chr(10))
    varOutC = Split(ws.Range("C" & i).Value, Chr(10))
    If UBound(varOutB) > 1 Then
        If UBound(varOutB) <> UBound(varOutC) Then
            ws.Range("D" & i).Value = "Unable to split!"
        Else
            For j = LBound(varOutB) To UBound(varOutB)
                k = ws.Range("F" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
                ws.Range("F" & k).Value = ws.Range("A" & i).Value
                ws.Range("G" & k).Value = varOutB(j)
                ws.Range("H" & k).Value = varOutC(j)
            Next j
            ws.Range("D" & i).Value = "Split Successfully!"
        End If
    Else
        k = ws.Range("F" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
        ws.Range("F" & k).Value = ws.Range("A" & i).Value
        ws.Range("G" & k).Value = ws.Range("B" & i).Value
        ws.Range("H" & k).Value = ws.Range("C" & i).Value
        ws.Range("D" & i).Value = "No split required!"
    End If
Next
Application.ScreenUpdating = True

End Sub

W.O.W. I didn't think it would of been that difficult! I can't wait to try this in the morning!! No wonder I couldn't figure it out. What action would you describe it? Obviously not transpose. Kelli

 

[shrivallabha]

W.O.W. I didn't think it would of been that difficult! I can't wait to try this in the morning!! No wonder I couldn't figure it out. What action would you describe it? Obviously not transpose. Kelli

Sledgehammer
It is plain data splitting and then pasting in individual cells.

 

[GraH - Guido]

Alternative with Power Query.

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  ZipListsFromText = Table.AddColumn(Source, "CombineLists", each List.Zip({Text.Split([Course Name],"#(lf)"),Text.Split([Course Status],"#(lf)")})),
  #"Expanded CombineLists" = Table.ExpandListColumn(ZipListsFromText, "CombineLists"),
  #"Extracted Values" = Table.TransformColumns(#"Expanded CombineLists", {"CombineLists", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "CombineLists", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"CombineLists.1", "CombineLists.2"}),
  #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Course Name", "Course Status"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CombineLists.1", "Course Name"}, {"CombineLists.2", "Course Status"}})
in
  #"Renamed Columns"

 

[Kelli Webb]

Sledgehammer
It is plain data splitting and then pasting in individual cells.

Thank you Shrivallabha, this worked perfectly.

 

[rahulshewale1]

Dear Kelli ,


please go through formula solution

i would suggest use power query or vba solution as already told Excel ninja