Delimited data in two columns, need to combine data between the two columns

[kouki]

Hi all, first question! I'm hopeful I'll get some tips that will help... Fingers crossed!

My delimted data looks as per the screenshot below - what I have is at the top (rows 1 and 2 in the image), and I what I want to get to is at the bottom (from row 7)

Data are over 20K rows long. The elements aren't always of the same length, as illustrated (hopefully!) in my example above.

I've tried switching text to columns, experimenting with pivot tables, tried playing around with transposing, but no luck and I'm confusing myself.

Any tips would be really greatly appreciated

 

[Micron]

Being a vba (mostly Access though) person I lean towards a code solution that
- sets lngStartName and lngStartID to 1
- finds the position of the first redirect pipe ( | ) in A2 and assigns to variable lngEndName
- finds the position of the first redirect pipe ( | ) in B2 and assigns to variable lngEndID
- sets start position for Mid function to lngStartName and gets mid of A2 (length is lngEndName); assign to strName
- sets start position for Mid function to lngStartID and gets mid of B2 (length is lngEndID); assign to strID
- adds each resulting string to a 2 dimensional array
- adds lngEndID to lngStartID and lngEndName to lngStartName; repeat from step 2 to find next pipe.
Mid would then use new start positions. When there is no result from trying to find next pipe (because there isn't one), move down one row.

All that is high level and doesn't cover any Trim of spaces, adjustments to the start/end positions because of those spaces, creating/adding/redim of array, determining number of rows, perhaps dealing with mismatched counts of ID's versus Names, etc. etc.
The etc's are things that I can't even foresee.

 

[JoeMo]

This works provided the average number of names per cell in the input data doesn't get large enough to require more output rows than available (~ 1E6). Output goes to columns D & E.
For 20,000 rows of data with 3 names per row, on my computer, it takes about 7-8 seconds to run.

Sub ReorgData()
'assumes raw data in cols A & B with header "Names" in A1
Dim Rnms As Range, Vnms As Variant
Dim i As Long, j As Long, ct As Long, S1 As Variant, S2 As Variant, nxRw As Long
With Range("D:E")
    .ClearContents
    .Cells(1, 1).Resize(1, 2).Value = Array("Name", "ID")
End With
Set Rnms = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Vnms = Rnms.Value
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
For i = LBound(Vnms, 1) To UBound(Vnms, 1)
    S1 = Split(Vnms(i, 1), " | ")
    S2 = Split(Vnms(i, 2), " | ")
    nxRw = Cells(Rows.Count, "D").End(xlUp).Row + 1
    Range("D" & nxRw).Resize(UBound(S1) + 1).Value = Application.Transpose(S1)
    Range("E" & nxRw).Resize(UBound(S2) + 1).Value = Application.Transpose(S2)
Next i
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub