Data Manipulation.

[Wilson]

Any help with this would be very much appreciated... a few months ago, Don
Guillet provided a macro (see at the bottom) as a solution to a problem I
had. At the time, this solved my issue, but I now have additional
requirements that I'm hoping someone will be able to assist with....

I pasted MSP data into 2 excel columns (A: Task name | B: Task Successors
*[separated by commas]*) for example:

Execute Assembly Test - FI-EFT | 3612,3690
Execute Assembly Test - FI-Unclaimed Monies | 3616,3694,4730
Execute Assembly Test - FI-Send to Coll. Agency | 1934,3635,3717,4743
Execute Assembly Test - CS-Inbound E-mail | 3680,4722
-
Don provided me a solution to get the data into the following format (each
successor now placed into its own row directly underneath the task name that
it succeeds)

Execute Assembly Test - FI-EFT
3612
3690
Execute Assembly Test - FI-Unclaimed Monies
3616
3694
4730

Here is the macro:
Sub dotexttocolumns()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & lr).TextToColumns Destination:= _
Range("B2"), DataType:=xlDelimited, Comma:=True
For i = lr To 2 Step -1
lc = Cells(i, Columns.Count).End(xlToLeft).Column
If lc > 1 Then Rows(i + 1).Resize(lc - 1).Insert
Cells(i, 1).Resize(1, lc).Copy
Cells(i + 1, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
End Sub

MY NEW PROBLEM is that the task name doesn't also repeat in each new row...
I'd like to be able to manipulate the data like this....

Execute Assembly Test - FI-EFT
Execute Assembly Test - FI-EFT | 3612
Execute Assembly Test - FI-EFT | 3690
Execute Assembly Test - FI-Unclaimed Monies
Execute Assembly Test - FI-Unclaimed Monies | 3616
Execute Assembly Test - FI-Unclaimed Monies | 3694
Execute Assembly Test - FI-Unclaimed Monies | 4730

**** not that this should matter, but I'll include it just in case...
I'm planning to replace "TaskName" with "TaskID" therefore... the result
would look more like this.

Paste into Excel
Task 1 = 10002 | 3612,3690
Task 2 = 61200 | 3616,3694,4730

Run Macro... result:
10002
10002 | 3612
10002 | 3690
61200
61200 | 3616
61200 | 3694
61200 | 4730

Any help with this would be much appreciated. Thanks.