Convert concatenated cell into multiple rows, whilst repeating non-concatenated adjacent cells

[TheTallBloke]

Hi all,

I've got a massive sales order report where all multiple row data (products, quantity, prices) belonging to the same order have been concatenated in a single cell (rather than a nice, structured 1/row that I could pivot easily).

Can you please help with converting all the concatenated data (always delimited by the colon symbol) breaks into separate rows, whilst the non-concatenated data gets repeated against them?
If possible I would like to be able to specify the columns where concatenated data is expected, to make this scalable.

Many thanks in advance!


Example below of before/after:

order number	product id	product name	product price	product quantity	order total	etc
1​	1	a	100.00	50.00	5000​	abc
2​	2:3	b:c	100.00:26.50	4.00:8.50	625.25​	def
3​	4:5:6	d:e:f	200.50:45.50:72.00	1.00:1.00:2.50	426​	ghi

order number	product id	product name	product price	product quantity	order total	etc
1​	1	a	100.00	50.00	5000​	abc
2​	2	b	100.00	4.00	625.25​	def
2​	3	c	26.50	8.50	625.25​	def
3​	4	d	200.50	1.00	426​	ghi
3​	5	e	45.50	1.00	426​	ghi
3​	6	f	72.00	2.50	426​	ghi

 

[Marc L]

Hi,​

​

where should be the expected result, in same worksheet ?​

 

[Marc L]

A demonstration as a beginner starter :​

Sub Demo1()
  Const D = ":"
    Dim R&, N&, Rg As Range
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Rows
        For R = .Count To 2 Step -1
               N = UBound(Split(.Cells(R, 2).Text, D))
            If N Then
                   .Item(R).Copy:  .Item(R + 1).Resize(N).Insert xlDown
                For Each Rg In .Item(R).Resize(N + 1).Columns
                    If InStr(Rg.Cells(1).Text, D) Then Rg.Value2 = Application.Transpose(Split(Rg.Cells(1).Text, D))
                Next
            End If
        Next
    End With
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
End Sub

 

[TheTallBloke]

Sorry about the delay, crazy last few days and I missed the notification completely.

New worksheet or same worksheet - whatever is easier.

I'm a bit confused about how to run the above - I've added it as a module and tried to run it from the VBA window but nothing seems to be happening? Thanks in advance.

 

[Marc L]

​

Well works on my side according to your initial post sample data from the active sheet to the same active sheet …​

 

[TheTallBloke]

Sorry, I may be too much of a newbie to VBA to know how to run it properly, would you be able to guide me please?

 

[Marc L]

Just paste the source data to an Excel empty worksheet then paste the code in a standard module or in the worksheet module whatever​

then you can execute the demonstration from the worksheet via the Developer tab or via the key combo Alt + F8 …​

​

​