Create List

[Wilson]

I'm trying to create a list from the following data (Names are in column 1
and color values are in Column 2 and separated by commas):

Adam | Blue, Green
Eric | Red, Yellow
Steffani | Orange, Purple

I would like to create a list from the above data that returns

Adam
Blue
Green
Eric
Red
Yellow
Steffani
Orange
Purple

 

[Wilson]

I reconsidered my example and thought I would use real data. I have a
feeling this can't be accomplished with in-cell formulas and will require VBA
(which I'm unfamiliar with)... So, please have a look at the real data:

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

I would like the result to spit out a grouping / sub-grouping report

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

etc...

 

[Don Guillett]

Try this. Then just delete row 2 and the columns no longer needed. Or,
incorporate into 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

 

[Wilson]

Don -

That works well, except when there is 3+ variables in the second column. If
there are more than 3, it simply leaves the values unchanged for that
specific grouping and moves on to the next. Any thoughts?

Thanks again for your help!

 

[Wilson]

Scratch that... that isn't the reason, I still need to identify why it worked
for some and not for a few others. When I click on the cell (that contains
the values separated by comma) for the items that the macro didn't work
properly on, the value is actually represented by a single string of digits
in the formula bar. I'm not sure why some of the paste (from MSP) resulted
in the values being separated by a comma (this is the way I would think it
would work for everything) and not for a few others.

 

[Don Guillett]

I did test. Send your workbook to my address below if you want me to take a
look.
The first part of the code simply does a
data>text to columns>comma

 

[Wilson]

Don -

This is fantastic... your macro has really helped me out! thanks!

I have a new twist though....I need some additional functionality. Any help
would be appreciated.

I need to Copy this data from MSP into Excel:
ID|Name|Duration|Rem. Duration|Successors|Finish Date|Actual Finish

#302|Task E|9 d|2 d|#1200,#475,#949|08-14-08|N/A
#420|Task K|7 d|7 d|#520,#2003,#399,#293,#378|08-21-08|N/A

Run a version of your macro to spit out the following:

Row1:Task Detail for #302 (i.e. Task E|9 d|2d|...)
Row2:#1200
Row3:#475
Row4:#949
Row5:Task Detail for #420
Row6:#520
Row7:#2003
Row8:#399
Row9:#293
Row10:#378

Currently... the macro will take the successors that are stored in a single
field (separated by commas) and assign them each to a row directly below;
however, I'm not able to retain the additional task detail. With the current
Macro, I can only have two columns of data (i.e. Name | successor or ID |
successor)...

 

[Otto Moehrbach]

Post your responses in the original thread. HTH Otto