Create List

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wilson - 20 May 2008 13:39 GMT

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

Reply to this Message

Wilson - 20 May 2008 14:13 GMT

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...

Reply to this Message

Don Guillett - 20 May 2008 15:35 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>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
[quoted text clipped - 19 lines]
>
> etc...

Reply to this Message

Wilson - 20 May 2008 17:29 GMT

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!

Reply to this Message

Wilson - 20 May 2008 17:38 GMT

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.

Reply to this Message

Don Guillett - 20 May 2008 19:12 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Scratch that... that isn't the reason, I still need to identify why it
> worked
[quoted text clipped - 7 lines]
> 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.

Reply to this Message

Wilson - 12 Aug 2008 19:01 GMT

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)...

Reply to this Message

Otto Moehrbach - 12 Aug 2008 19:16 GMT

Post your responses in the original thread. HTH Otto

> Don -
>
[quoted text clipped - 30 lines]
> Macro, I can only have two columns of data (i.e. Name | successor or ID |
> successor)...

Reply to this Message