VBA Transpose and separate comma separated values

[decadence]

Hi, I am trying to separate comma separated text in multiple cells in a column but would like to retain the exact same date for each row of separated text except for Quantity and the transposed references. Also I will have varied rows of data in the reference column, Can some one help with this please.

From this

Header	Header	Header	Header	Reference	Quantity
There	are	some	Apples	Red,Green,Yellow,Blue	4
This	is not	an	Orange	White,Black	2

<tbody>
</tbody>

to this

Header	Header	Header	Header	Reference	Quantity
There	are	some	Apples	Red	1
There	are	some	Apples	Green	1
There	are	some	Apples	Yellow	1
There	are	some	Apples	Blue	1
This	is not	an	Orange	White	1
This	is not	an	Orange	Black	1

<tbody>
</tbody>

 

[GTO]

Excel Workbook
	A	B	C	D	E	F
2	There	are	some	Apples	Red, Green,Yellow,Blue	5
Sheet1

Excel 2010

Will the number of reference always equal quantity, or might the above occur?

 

[MickG]

Try this for results sting "H1".

[COLOR="Navy"]Sub[/COLOR] MG04Sep44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Offset(, 4), ",")
    Cells(c, "H").Resize(UBound(Sp) + 1, 4).Value = Dn.Resize(, 4).Value
    Cells(c, "L").Resize(UBound(Sp) + 1).Value = IIf(UBound(Sp) > 0, Application.Transpose(Sp), Dn.Offset(, 4).Value)
    Cells(c, "M").Resize(UBound(Sp) + 1).Value = 1
    c = c + UBound(Sp) + 1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[decadence]

Hi GTO, Yes it may occur as the data is varied.

Hi MickG, Your code works, but can it be changed so the headers are not included so it starts in row 2, also is it possible to do it in the same columns and get the same result as your code? Many Thanks

 

[MickG]

Change the second line to range("A2") from range("A1").

also is it possible to do it in the same columns and get the same result as your code?

I'm not sure what this means ???