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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wilson" wrote in message
...
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 -

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!

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.

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
datatext to columnscomma

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wilson" wrote in message
...
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.