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.

A	B	C	D	E
51	Bob	PPV	15	9
61	James	CHH	30|16|5	17|5|8
22	Steve	PPV	55|2	-14|125
5	Ivan	DCh	6	-7

<tbody>
</tbody>

I'd like the table to look like:

A	B	C	D	E
51	Bob	ppv	15	9
61	James	CHH	30	17
61	James	CHH	16	5
61	James	CHH	5	8
22	Steve	PPV	55	-14
22	Steve	PPV	2	125
5	Ivan	DCh	6	-7

<tbody>
</tbody>

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!