Help converting two delimited columns into rows!! Arg!

[Mathorik]

Hello All!

I have a table with date in two columns (D&E) that have linked delimited data. If column D has 2 values separated by a "|" delimiter, then column E will have the same number of delimited values.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Bob[/TD]
[TD]PPV[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]James[/TD]
[TD]CHH[/TD]
[TD]30|16|5[/TD]
[TD]17|5|8[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Steve[/TD]
[TD]PPV[/TD]
[TD]55|2[/TD]
[TD]-14|125[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ivan[/TD]
[TD]DCh[/TD]
[TD]6[/TD]
[TD]-7[/TD]
[/TR]
</tbody>[/TABLE]

I'd like the table to look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Bob[/TD]
[TD]ppv[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]James[/TD]
[TD]CHH[/TD]
[TD]30[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]James[/TD]
[TD]CHH[/TD]
[TD]16[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]James[/TD]
[TD]CHH[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Steve[/TD]
[TD]PPV[/TD]
[TD]55[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Steve[/TD]
[TD]PPV[/TD]
[TD]2[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ivan[/TD]
[TD]DCh[/TD]
[TD]6[/TD]
[TD]-7[/TD]
[/TR]
</tbody>[/TABLE]


I'd like a macro to help out, as this process will be occurring weekly with a few thousand rows...
I've seen a few examples on here- but nothing with the two linked columns.
Thank you all in advance for your brain power!

Eric

 

[VBA Geek]

having your data beginning in A1, with headers, try

[COLOR=darkblue]Sub[/COLOR] trnsp()
[COLOR=darkblue]Dim[/COLOR] vArr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], tArr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], x1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], y1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], k1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], spltE, spltD


vArr = Range("A1").CurrentRegion.Value


[COLOR=darkblue]ReDim[/COLOR] tArr(1 [COLOR=darkblue]To[/COLOR] Evaluate(Replace("SUM(LEN(D2:D@)-LEN(SUBSTITUTE(D2:D@,""|"",""""))+1)", "@", Range("A" & Rows.Count).End(xlUp).Row)), 1 [COLOR=darkblue]To[/COLOR] 5)


[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] [COLOR=darkblue]UBound[/COLOR](vArr, 1)-1 = [COLOR=darkblue]UBound[/COLOR](tArr, 1) [COLOR=darkblue]Then[/COLOR]
    k1 = 1
    [COLOR=darkblue]For[/COLOR] x1 = [COLOR=darkblue]LBound[/COLOR](vArr, 1) + 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vArr, 1)
        spltD = Split(vArr(x1, 4), "|")
        spltE = Split(vArr(x1, 5), "|")
        [COLOR=darkblue]For[/COLOR] y1 = [COLOR=darkblue]LBound[/COLOR](spltD) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](spltD)
            tArr(k1, 1) = vArr(x1, 1)
            tArr(k1, 2) = vArr(x1, 2)
            tArr(k1, 3) = vArr(x1, 3)
            tArr(k1, 4) = spltD(y1)
            tArr(k1, 5) = spltE(y1)
            k1 = 1 + k1
        [COLOR=darkblue]Next[/COLOR] y1
    [COLOR=darkblue]Next[/COLOR] x1
Range("a2").Resize(UBound(tArr, 1), 5).Value = tArr
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

msgbox "Done"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Mathorik]

VBA Geek- getting a Subscript out of Range
at tArr(k1, 5) = spltE(y1)

 

[VBA Geek]

click on Debug and put your cursor on x1 and check its value, then go back to your data and check if column D and E on Row x1 have the same number of "|"

 

[Mathorik]

Found a blank value in column E on that line number. Once that was resolved, the program ran without error!
Excellent! Thank you VBA Geek!