Seperating data by comma and adding rows.

johnmpc

New Member
Joined
Oct 19, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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?

UC101220GSM Classic Pique PoloshirtBlack, Bottle Green, Charcoal, Heather Grey, Hot Pink, French Navy, Kelly Green, Maroon, Navy, Orange, Purple, Red, Royal, Sapphire, Sky, White, Yellow£2.60
UC102250GSM Premium Pique PoloshirtBlack, Bottle Green, Heather Grey, French Navy, Maroon, Navy, Red, Royal, Sky, White, Yellow£4.00
UC103220GSM Childrens Pique PoloshirtBlack, Bottle Green, Brown, Heather Grey, Maroon, Navy, Purple, Red, Royal, Sky, White, Yellow£2.50
UC104250GSM Ultimate Pique PoloshirtBlack, Bottle Green, Navy, Red, Royal, White£4.55
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Assuming there is a heading row in row 1 and data starts in row 2, try this with a copy of your data.

VBA Code:
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
ABCDEFGHI
1
2UC101220GSM Classic Pique PoloshirtBlack, Bottle Green, Charcoal, Heather Grey, Hot Pink, French Navy, Kelly Green, Maroon, Navy, Orange, Purple, Red, Royal, Sapphire, Sky, White, Yellow£2.60UC101220GSM Classic Pique PoloshirtBlack£2.60
3UC102250GSM Premium Pique PoloshirtBlack, Bottle Green, Heather Grey, French Navy, Maroon, Navy, Red, Royal, Sky, White, Yellow£4.00UC101220GSM Classic Pique PoloshirtBottle Green£2.60
4UC103220GSM Childrens Pique PoloshirtBlack, Bottle Green, Brown, Heather Grey, Maroon, Navy, Purple, Red, Royal, Sky, White, Yellow£2.50UC101220GSM Classic Pique PoloshirtCharcoal£2.60
5UC104250GSM Ultimate Pique PoloshirtBlack, Bottle Green, Navy, Red, Royal, WhiteUC101220GSM Classic Pique PoloshirtHeather Grey£2.60
6UC101220GSM Classic Pique PoloshirtHot Pink£2.60
7UC101220GSM Classic Pique PoloshirtFrench Navy£2.60
8UC101220GSM Classic Pique PoloshirtKelly Green£2.60
9UC101220GSM Classic Pique PoloshirtMaroon£2.60
10UC101220GSM Classic Pique PoloshirtNavy£2.60
11UC101220GSM Classic Pique PoloshirtOrange£2.60
12UC101220GSM Classic Pique PoloshirtPurple£2.60
13UC101220GSM Classic Pique PoloshirtRed£2.60
14UC101220GSM Classic Pique PoloshirtRoyal£2.60
15UC101220GSM Classic Pique PoloshirtSapphire£2.60
16UC101220GSM Classic Pique PoloshirtSky£2.60
17UC101220GSM Classic Pique PoloshirtWhite£2.60
18UC101220GSM Classic Pique PoloshirtYellow£2.60
19UC102250GSM Premium Pique PoloshirtBlack£4.00
20UC102250GSM Premium Pique PoloshirtBottle Green£4.00
21UC102250GSM Premium Pique PoloshirtHeather Grey£4.00
22UC102250GSM Premium Pique PoloshirtFrench Navy£4.00
23UC102250GSM Premium Pique PoloshirtMaroon£4.00
24UC102250GSM Premium Pique PoloshirtNavy£4.00
25UC102250GSM Premium Pique PoloshirtRed£4.00
26UC102250GSM Premium Pique PoloshirtRoyal£4.00
27UC102250GSM Premium Pique PoloshirtSky£4.00
28UC102250GSM Premium Pique PoloshirtWhite£4.00
29UC102250GSM Premium Pique PoloshirtYellow£4.00
30UC103220GSM Childrens Pique PoloshirtBlack£2.50
31UC103220GSM Childrens Pique PoloshirtBottle Green£2.50
32UC103220GSM Childrens Pique PoloshirtBrown£2.50
33UC103220GSM Childrens Pique PoloshirtHeather Grey£2.50
34UC103220GSM Childrens Pique PoloshirtMaroon£2.50
35UC103220GSM Childrens Pique PoloshirtNavy£2.50
36UC103220GSM Childrens Pique PoloshirtPurple£2.50
37UC103220GSM Childrens Pique PoloshirtRed£2.50
38UC103220GSM Childrens Pique PoloshirtRoyal£2.50
39UC103220GSM Childrens Pique PoloshirtSky£2.50
40UC103220GSM Childrens Pique PoloshirtWhite£2.50
41UC103220GSM Childrens Pique PoloshirtYellow£2.50
42
Sheet1
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You are very welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,449
Messages
5,596,212
Members
414,046
Latest member
mbeutler1203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top