Text to column and then stack

J

JV0710

Active Member
Joined
Oct 26, 2006
Messages
440
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Good Day

Please could I get some help with the following :

Below is a sample of my data table

In column B - I would like to find a way to split into columns ( where applicable - comma delimiter) and then stack the data
so, for example - row 6 will be repeated 6 times with the unique names in cell b6 - as there are 6 names in cell B6

TFG Textile Labs Oct 2023.xlsm
ABCD
1LabNumberTest NamesColourDateIn
2LE031023015Print Durability (ETM 07) Grey 03 10 2023 14:50
3LE031023016Colour Fastness to Washing (SANS 11166) White 03 10 2023 14:55
4LE280923016Seam Slippage - Product Black 28 09 2023 14:10
5LE280923015Seam Slippage - Product Beige 28 09 2023 13:47
6LE280923018Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) , Pilling (SANS 6116) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Light Pink 28 09 2023 14:30
7LE190923041Print Durability (ETM 07) White , White , White 19 09 2023 15:20
8LE150923017Mass (SANS 79) Grey , Navy , Khaki 15 09 2023 14:01
9LE210923016Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Print Durability (ETM 07) Dark Blue 21 09 2023 14:39
10LE290923024Print Durability (ETM 07) Taupe , Taupe 29 09 2023 14:27
11LE041023018Seam Slippage - Product White 04 10 2023 12:16
12LE280923008Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) Blue 28 09 2023 10:29
13LE290923009Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Black 29 09 2023 11:31
14LE021023001Mass - with spec (SANS 79) , Pilling (SANS 6116) Black 02 10 2023 08:22
15LE280923014Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Spirality (ETM 01) White 28 09 2023 13:47
16LE280923017Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) White 28 09 2023 14:25
17LE290923025Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) White 29 09 2023 15:24
18LE290923008Mass - with spec (SANS 79) , Pilling (SANS 6116) White 29 09 2023 11:19
19LE290923013Abrasion Resistance (SANS 6009) , Tensile Strength (ISO 13934-1) , Seam Slippage (SANS 6194) , Mass - with spec (SANS 79) Cream 29 09 2023 12:00
20LE021023005Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Colour Fastness to Washing (SANS 11166) Blue 02 10 2023 13:13
21LE021023006Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Blue 02 10 2023 13:29
22LE021023003Print Durability (ETM 07) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Spirality (ETM 01) , Mass - with spec (SANS 79) White 02 10 2023 09:59
23LE051023002Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Two tone 05 10 2023 08:06
Sheet1



Result for row 6

TFG Textile Labs Oct 2023.xlsm
ABCD
27LE280923018Mass - with spec (SANS 79) Light Pink 45197.60449
28LE280923018Dimensional Stability (SANS 11167) Light Pink 45197.60449
29LE280923018Spirality (ETM 01) Light Pink 45197.60449
30LE280923018 Pilling (SANS 6116) Light Pink 45197.60449
31LE280923018Colour Fastness to Rubbing - Dry (ISO 105 - X12) Light Pink 45197.60449
32LE280923018Colour Fastness to Washing (SANS 11166) Light Pink 45197.60449
Sheet1


Thanks in advance for the help


Joe
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Give this a try with a copy of your workbook. It puts the results in columns F:I

VBA Code: 
Sub Split_n_Stack()
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To Rows.Count, 1 To 4)
  For i = 1 To UBound(a)
    For Each itm In Split(Replace(a(i, 2), ", ", ","), ",")
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = itm: b(k, 3) = a(i, 3): b(k, 4) = a(i, 4)
    Next itm
  Next i
  Range("F1:I1").Resize(k).Value = b
End Sub

My sample data and part of results.

JV0710.xlsm
ABCDEFGHI
1LabNumberTest NamesColourDateInLabNumberTest NamesColourDateIn
2LE031023015Print Durability (ETM 07) Grey 45202.61872LE031023015Print Durability (ETM 07) Grey 45202.61872
3LE031023016Colour Fastness to Washing (SANS 11166) White 45202.62154LE031023016Colour Fastness to Washing (SANS 11166) White 45202.62154
4LE280923016Seam Slippage - Product Black 45197.59032LE280923016Seam Slippage - Product Black 45197.59032
5LE280923015Seam Slippage - Product Beige 45197.5748LE280923015Seam Slippage - Product Beige 45197.5748
6LE280923018Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) , Pilling (SANS 6116) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Light Pink 45197.60449LE280923018Mass - with spec (SANS 79) Light Pink 45197.60449
7LE190923041Print Durability (ETM 07) White , White , White 45188.63937LE280923018 Dimensional Stability (SANS 11167) Light Pink 45197.60449
8LE150923017Mass (SANS 79) Grey , Navy , Khaki 45184.58434LE280923018 Spirality (ETM 01) Light Pink 45197.60449
9LE210923016Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Print Durability (ETM 07) Dark Blue 45190.61057LE280923018 Pilling (SANS 6116) Light Pink 45197.60449
10LE290923024Print Durability (ETM 07) Taupe , Taupe 45198.60256LE280923018 Colour Fastness to Rubbing - Dry (ISO 105 - X12) Light Pink 45197.60449
11LE041023018Seam Slippage - Product White 45203.51139LE280923018 Colour Fastness to Washing (SANS 11166) Light Pink 45197.60449
12LE280923008Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) Blue 45197.43709LE190923041Print Durability (ETM 07) White , White , White 45188.63937
13LE290923009Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Black 45198.48005LE150923017Mass (SANS 79) Grey , Navy , Khaki 45184.58434
14LE021023001Mass - with spec (SANS 79) , Pilling (SANS 6116) Black 45201.34885LE210923016Colour Fastness to Rubbing - Dry (ISO 105 - X12) Dark Blue 45190.61057
15LE280923014Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Spirality (ETM 01) White 45197.57441LE210923016 Colour Fastness to Washing (SANS 11166) Dark Blue 45190.61057
16LE280923017Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) White 45197.60132LE210923016 Mass - with spec (SANS 79) Dark Blue 45190.61057
17LE290923025Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) White 45198.64209LE210923016 Spirality (ETM 01) Dark Blue 45190.61057
18LE290923008Mass - with spec (SANS 79) , Pilling (SANS 6116) White 45198.47182LE210923016 Dimensional Stability (SANS 11167) Dark Blue 45190.61057
19LE290923013Abrasion Resistance (SANS 6009) , Tensile Strength (ISO 13934-1) , Seam Slippage (SANS 6194) , Mass - with spec (SANS 79) Cream 45198.50037LE210923016 Print Durability (ETM 07) Dark Blue 45190.61057
20LE021023005Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Colour Fastness to Washing (SANS 11166) Blue 45201.55121LE290923024Print Durability (ETM 07) Taupe , Taupe 45198.60256
21LE021023006Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Blue 45201.56212LE041023018Seam Slippage - Product White 45203.51139
22LE021023003Print Durability (ETM 07) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Spirality (ETM 01) , Mass - with spec (SANS 79) White 45201.41608LE280923008Spirality (ETM 01) Blue 45197.43709
23LE051023002Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Two tone 45204.33786LE280923008 Colour Fastness to Rubbing - Dry (ISO 105 - X12) Blue 45197.43709
24LE280923008 Colour Fastness to Washing (SANS 11166) Blue 45197.43709
25LE280923008 Mass - with spec (SANS 79) Blue 45197.43709
26LE280923008 Dimensional Stability (SANS 11167) Blue 45197.43709
27LE290923009Dimensional Stability (SANS 11167) Black 45198.48005
28LE290923009 Colour Fastness to Rubbing - Dry (ISO 105 - X12) Black 45198.48005
29LE290923009 Mass - with spec (SANS 79) Black 45198.48005
30LE290923009 Spirality (ETM 01) Black 45198.48005
31LE290923009 Colour Fastness to Washing (SANS 11166) Black 45198.48005
32LE021023001Mass - with spec (SANS 79) Black 45201.34885
33LE021023001 Pilling (SANS 6116) Black 45201.34885
34LE280923014Dimensional Stability (SANS 11167) White 45197.57441
35LE280923014 Mass - with spec (SANS 79) White 45197.57441
36LE280923014 Spirality (ETM 01) White 45197.57441
37LE280923017Mass - with spec (SANS 79) White 45197.60132
38LE280923017 Spirality (ETM 01) White 45197.60132
39LE280923017 Dimensional Stability (SANS 11167) White 45197.60132
40LE290923025Mass - with spec (SANS 79) White 45198.64209
Sheet1
 
Upvote 0
Or if you prefer Power Query...
Book1
ABCD
1LabNumberTest NamesColourDateIn
2LE031023015Print Durability (ETM 07) Grey 3/10/2023
3LE031023016Colour Fastness to Washing (SANS 11166) White 3/10/2023
4LE280923016Seam Slippage - Product Black 28/09/2023
5LE280923015Seam Slippage - Product Beige 28/09/2023
6LE280923018Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) , Pilling (SANS 6116) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Light Pink 28/09/2023
7LE190923041Print Durability (ETM 07) White , White , White 19/09/2023
8LE150923017Mass (SANS 79) Grey , Navy , Khaki 15/09/2023
9LE210923016Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Print Durability (ETM 07) Dark Blue 21/09/2023
10LE290923024Print Durability (ETM 07) Taupe , Taupe 29/09/2023
11LE041023018Seam Slippage - Product White 4/10/2023
12LE280923008Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) Blue 28/09/2023
13LE290923009Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Black 29/09/2023
14LE021023001Mass - with spec (SANS 79) , Pilling (SANS 6116) Black 2/10/2023
15LE280923014Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Spirality (ETM 01) White 28/09/2023
16LE280923017Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) White 28/09/2023
17LE290923025Mass - with spec (SANS 79) , Dimensional Stability (SANS 11167) , Spirality (ETM 01) White 29/09/2023
18LE290923008Mass - with spec (SANS 79) , Pilling (SANS 6116) White 29/09/2023
19LE290923013Abrasion Resistance (SANS 6009) , Tensile Strength (ISO 13934-1) , Seam Slippage (SANS 6194) , Mass - with spec (SANS 79) Cream 29/09/2023
20LE021023005Spirality (ETM 01) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Dimensional Stability (SANS 11167) , Mass - with spec (SANS 79) , Colour Fastness to Washing (SANS 11166) Blue 2/10/2023
21LE021023006Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Mass - with spec (SANS 79) , Spirality (ETM 01) , Colour Fastness to Washing (SANS 11166) Blue 2/10/2023
22LE021023003Print Durability (ETM 07) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) , Spirality (ETM 01) , Mass - with spec (SANS 79) White 2/10/2023
23LE051023002Mass - with spec (SANS 79) , Spirality (ETM 01) , Dimensional Stability (SANS 11167) , Colour Fastness to Rubbing - Dry (ISO 105 - X12) , Colour Fastness to Washing (SANS 11166) Two tone 5/10/2023
24
25LabNumberTest NamesColourDateIn
26LE031023015Print Durability (ETM 07) Grey 3/10/2023
27LE031023016Colour Fastness to Washing (SANS 11166) White 3/10/2023
28LE280923016Seam Slippage - Product Black 28/09/2023
29LE280923015Seam Slippage - Product Beige 28/09/2023
30LE280923018Mass - with spec (SANS 79) Light Pink 28/09/2023
31LE280923018Dimensional Stability (SANS 11167) Light Pink 28/09/2023
32LE280923018Spirality (ETM 01) Light Pink 28/09/2023
33LE280923018Pilling (SANS 6116) Light Pink 28/09/2023
34LE280923018Colour Fastness to Rubbing - Dry (ISO 105 - X12) Light Pink 28/09/2023
35LE280923018Colour Fastness to Washing (SANS 11166) Light Pink 28/09/2023
36LE190923041Print Durability (ETM 07) White , White , White 19/09/2023
37LE150923017Mass (SANS 79) Grey , Navy , Khaki 15/09/2023
38LE210923016Colour Fastness to Rubbing - Dry (ISO 105 - X12) Dark Blue 21/09/2023
39LE210923016Colour Fastness to Washing (SANS 11166) Dark Blue 21/09/2023
40LE210923016Mass - with spec (SANS 79) Dark Blue 21/09/2023
41LE210923016Spirality (ETM 01) Dark Blue 21/09/2023
42LE210923016Dimensional Stability (SANS 11167) Dark Blue 21/09/2023
43LE210923016Print Durability (ETM 07) Dark Blue 21/09/2023
44LE290923024Print Durability (ETM 07) Taupe , Taupe 29/09/2023
45LE041023018Seam Slippage - Product White 4/10/2023
46LE280923008Spirality (ETM 01) Blue 28/09/2023
47LE280923008Colour Fastness to Rubbing - Dry (ISO 105 - X12) Blue 28/09/2023
48LE280923008Colour Fastness to Washing (SANS 11166) Blue 28/09/2023
49LE280923008Mass - with spec (SANS 79) Blue 28/09/2023
50LE280923008Dimensional Stability (SANS 11167) Blue 28/09/2023
51LE290923009Dimensional Stability (SANS 11167) Black 29/09/2023
52LE290923009Colour Fastness to Rubbing - Dry (ISO 105 - X12) Black 29/09/2023
53LE290923009Mass - with spec (SANS 79) Black 29/09/2023
54LE290923009Spirality (ETM 01) Black 29/09/2023
55LE290923009Colour Fastness to Washing (SANS 11166) Black 29/09/2023
56LE021023001Mass - with spec (SANS 79) Black 2/10/2023
57LE021023001Pilling (SANS 6116) Black 2/10/2023
58LE280923014Dimensional Stability (SANS 11167) White 28/09/2023
59LE280923014Mass - with spec (SANS 79) White 28/09/2023
60LE280923014Spirality (ETM 01) White 28/09/2023
61LE280923017Mass - with spec (SANS 79) White 28/09/2023
62LE280923017Spirality (ETM 01) White 28/09/2023
63LE280923017Dimensional Stability (SANS 11167) White 28/09/2023
64LE290923025Mass - with spec (SANS 79) White 29/09/2023
65LE290923025Dimensional Stability (SANS 11167) White 29/09/2023
66LE290923025Spirality (ETM 01) White 29/09/2023
67LE290923008Mass - with spec (SANS 79) White 29/09/2023
68LE290923008Pilling (SANS 6116) White 29/09/2023
69LE290923013Abrasion Resistance (SANS 6009) Cream 29/09/2023
70LE290923013Tensile Strength (ISO 13934-1) Cream 29/09/2023
71LE290923013Seam Slippage (SANS 6194) Cream 29/09/2023
72LE290923013Mass - with spec (SANS 79) Cream 29/09/2023
73LE021023005Spirality (ETM 01) Blue 2/10/2023
74LE021023005Colour Fastness to Rubbing - Dry (ISO 105 - X12) Blue 2/10/2023
75LE021023005Dimensional Stability (SANS 11167) Blue 2/10/2023
76LE021023005Mass - with spec (SANS 79) Blue 2/10/2023
77LE021023005Colour Fastness to Washing (SANS 11166) Blue 2/10/2023
78LE021023006Dimensional Stability (SANS 11167) Blue 2/10/2023
79LE021023006Colour Fastness to Rubbing - Dry (ISO 105 - X12) Blue 2/10/2023
80LE021023006Mass - with spec (SANS 79) Blue 2/10/2023
81LE021023006Spirality (ETM 01) Blue 2/10/2023
82LE021023006Colour Fastness to Washing (SANS 11166) Blue 2/10/2023
83LE021023003Print Durability (ETM 07) White 2/10/2023
84LE021023003Dimensional Stability (SANS 11167) White 2/10/2023
85LE021023003Colour Fastness to Rubbing - Dry (ISO 105 - X12) White 2/10/2023
86LE021023003Colour Fastness to Washing (SANS 11166) White 2/10/2023
87LE021023003Spirality (ETM 01) White 2/10/2023
88LE021023003Mass - with spec (SANS 79) White 2/10/2023
89LE051023002Mass - with spec (SANS 79) Two tone 5/10/2023
90LE051023002Spirality (ETM 01) Two tone 5/10/2023
91LE051023002Dimensional Stability (SANS 11167) Two tone 5/10/2023
92LE051023002Colour Fastness to Rubbing - Dry (ISO 105 - X12) Two tone 5/10/2023
93LE051023002Colour Fastness to Washing (SANS 11166) Two tone 5/10/2023
94
Sheet2
 
Upvote 0
Solution
Thanks very much Peter and Kevin

I am trying the PowerQuery Solution - using the Unpivot option after splitting the column., But I like your VB solution as well Peter - The user requiring my help here is using a MAC and I am not sure which will be a better way to go.

@Kevin - Is it possible to get a sample of the PowerQuery steps you took ? my result / Layout does not look exactly like yours does

Thanks once again - This will help greatly
 
Upvote 0
Glad we were able to help & thanks for the feedback :) (y)
This is probably the best & simplest I could find:
 
Upvote 0
You're welcome. Glad we could help. Good luck with your choice of options. :)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top