Seperating data by comma and adding rows.

[johnmpc]

Hi, I need some help please.

I have the below data, Column A is the Item Code, B is the description, C is the various colours, and D is the price.

I would like the colours to be on individual lines so each Code has multiple entries next to 1 colour option.
Any quick way of doing this?

UC101	220GSM Classic Pique Poloshirt	Black, Bottle Green, Charcoal, Heather Grey, Hot Pink, French Navy, Kelly Green, Maroon, Navy, Orange, Purple, Red, Royal, Sapphire, Sky, White, Yellow	£2.60
UC102	250GSM Premium Pique Poloshirt	Black, Bottle Green, Heather Grey, French Navy, Maroon, Navy, Red, Royal, Sky, White, Yellow	£4.00
UC103	220GSM Childrens Pique Poloshirt	Black, Bottle Green, Brown, Heather Grey, Maroon, Navy, Purple, Red, Royal, Sky, White, Yellow	£2.50
UC104	250GSM Ultimate Pique Poloshirt	Black, Bottle Green, Navy, Red, Royal, White	£4.55

 

[Peter_SSs]

Assuming there is a heading row in row 1 and data starts in row 2, try this with a copy of your data.

Sub Rearrange()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 4)
  For i = 1 To UBound(a)
    Bits = Split(Replace(a(i, 3), ", ", ","), ",")
    For j = 0 To UBound(Bits)
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = Bits(j): b(k, 4) = a(i, 4)
    Next j
  Next i
  Range("F2:I2").Resize(k).Value = b
End Sub

Here is my sample data and results. Is this what you wanted?

johnmpc.xlsm
	A	B	C	D	E	F	G	H	I
1
2	UC101	220GSM Classic Pique Poloshirt	Black, Bottle Green, Charcoal, Heather Grey, Hot Pink, French Navy, Kelly Green, Maroon, Navy, Orange, Purple, Red, Royal, Sapphire, Sky, White, Yellow	£2.60		UC101	220GSM Classic Pique Poloshirt	Black	£2.60
3	UC102	250GSM Premium Pique Poloshirt	Black, Bottle Green, Heather Grey, French Navy, Maroon, Navy, Red, Royal, Sky, White, Yellow	£4.00		UC101	220GSM Classic Pique Poloshirt	Bottle Green	£2.60
4	UC103	220GSM Childrens Pique Poloshirt	Black, Bottle Green, Brown, Heather Grey, Maroon, Navy, Purple, Red, Royal, Sky, White, Yellow	£2.50		UC101	220GSM Classic Pique Poloshirt	Charcoal	£2.60
5	UC104	250GSM Ultimate Pique Poloshirt	Black, Bottle Green, Navy, Red, Royal, White			UC101	220GSM Classic Pique Poloshirt	Heather Grey	£2.60
6						UC101	220GSM Classic Pique Poloshirt	Hot Pink	£2.60
7						UC101	220GSM Classic Pique Poloshirt	French Navy	£2.60
8						UC101	220GSM Classic Pique Poloshirt	Kelly Green	£2.60
9						UC101	220GSM Classic Pique Poloshirt	Maroon	£2.60
10						UC101	220GSM Classic Pique Poloshirt	Navy	£2.60
11						UC101	220GSM Classic Pique Poloshirt	Orange	£2.60
12						UC101	220GSM Classic Pique Poloshirt	Purple	£2.60
13						UC101	220GSM Classic Pique Poloshirt	Red	£2.60
14						UC101	220GSM Classic Pique Poloshirt	Royal	£2.60
15						UC101	220GSM Classic Pique Poloshirt	Sapphire	£2.60
16						UC101	220GSM Classic Pique Poloshirt	Sky	£2.60
17						UC101	220GSM Classic Pique Poloshirt	White	£2.60
18						UC101	220GSM Classic Pique Poloshirt	Yellow	£2.60
19						UC102	250GSM Premium Pique Poloshirt	Black	£4.00
20						UC102	250GSM Premium Pique Poloshirt	Bottle Green	£4.00
21						UC102	250GSM Premium Pique Poloshirt	Heather Grey	£4.00
22						UC102	250GSM Premium Pique Poloshirt	French Navy	£4.00
23						UC102	250GSM Premium Pique Poloshirt	Maroon	£4.00
24						UC102	250GSM Premium Pique Poloshirt	Navy	£4.00
25						UC102	250GSM Premium Pique Poloshirt	Red	£4.00
26						UC102	250GSM Premium Pique Poloshirt	Royal	£4.00
27						UC102	250GSM Premium Pique Poloshirt	Sky	£4.00
28						UC102	250GSM Premium Pique Poloshirt	White	£4.00
29						UC102	250GSM Premium Pique Poloshirt	Yellow	£4.00
30						UC103	220GSM Childrens Pique Poloshirt	Black	£2.50
31						UC103	220GSM Childrens Pique Poloshirt	Bottle Green	£2.50
32						UC103	220GSM Childrens Pique Poloshirt	Brown	£2.50
33						UC103	220GSM Childrens Pique Poloshirt	Heather Grey	£2.50
34						UC103	220GSM Childrens Pique Poloshirt	Maroon	£2.50
35						UC103	220GSM Childrens Pique Poloshirt	Navy	£2.50
36						UC103	220GSM Childrens Pique Poloshirt	Purple	£2.50
37						UC103	220GSM Childrens Pique Poloshirt	Red	£2.50
38						UC103	220GSM Childrens Pique Poloshirt	Royal	£2.50
39						UC103	220GSM Childrens Pique Poloshirt	Sky	£2.50
40						UC103	220GSM Childrens Pique Poloshirt	White	£2.50
41						UC103	220GSM Childrens Pique Poloshirt	Yellow	£2.50
42
Sheet1

 

[johnmpc]

THAT IS AWESOME!!!! Thank you!

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.