break one row into multiple rows for same value

[myshak]

Available spreadsheet

COL1 COL2
WILLIAM ANDY bemasotawtop
Mike candy 101054b 101050
Andy ae176love ae176sofa ae176chair
hello 101050

We want to make like following

Final Spread Sheet
Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair
hello 101050

Final spreadsheet
Col1 col2
William Andy bemasotawtop
Mike Candy 101054b
Mike Candy@hello 101050
Andy ae176love
Andy ae176sofa
Andy ae176chair

 

[Rick Rothstein \(MVP - VB\)]

Does this macro do what you want?

Sub SplitLines()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim NameText As String
Dim Parts() As String
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If InStr(.Cells(X, "B"), " ") Then
NameText = .Cells(X, "A")
Parts = Split(.Cells(X, "B").Value)
.Rows(X).Resize(UBound(Parts), 1).EntireRow.Insert
For Z = UBound(Parts) To 0 Step -1
.Cells(X + Z, "A") = NameText
.Cells(X + Z, "B") = Parts(Z)
Next
End If
Next
End With
End Sub

Rick