Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Manipulation.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data manipulation question | Excel Worksheet Functions | |||
Help with data manipulation | Excel Discussion (Misc queries) | |||
Need help with some data manipulation | Excel Worksheet Functions | |||
Data manipulation | Excel Discussion (Misc queries) | |||
Data manipulation help required ! | Excel Worksheet Functions |