Duplication of data onto new rows based on contents of cells which contain semi-colon separated entries

[ellison]

Hi, we are trying to tidy up some data and are working on reports which we get sent.
The information needs to be duplicated onto different rows based on the contents of the cell in Column B...

Column B is where the multiple Manufacturer Part Numbers (MPN's) are entered.
The MPN's have been trimmed of everything apart from their AlphaNumeric Characters and are separated by semi-colons. And there are semi-colon bookends at the beginning and end of each cell.

We have enclosed a small sample of data below to show a bit better what we are looking for (hopefully!).

Example Raw Information:

Raw Data
A	B	C	D	E	F	G	H
row	MPN-Original	Info-1	Info-2	Info-3	Info-4	Count of MPN's	Notes for The Results Sheet
2	;ABC123;	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	new source needed 14/12/2021	1	For Row 2, only 1 MPN so no need for this info to change in the results sheet
3	;XYZ789;501304;	Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex	2	For Row 3, 2 MPN's so this would need to be duplicated onto 2 different rows on the results sheet
4	;XYZ789;501304;	11/06/2023 herts	01/01/2020 herts			2	For Row 4, 2 MPN's which are the same as row 3 BUT there are different entries in the information fields. It would need to be duplicated onto 2 different rows (NB the row number is different to the one above)
5	;GHEPANJ874755;847834HFHF7;7569FT2YYT44D;	11/06/2023	allocated all stocks, now obsolete			3	For Row 5, 3 MPN's, so duplicated onto 3 rows

Results Sheet

Results Sheet
A	B	C	D	E	F	G
row	MPN-V2	Info-1	Info-2	Info-3	Info-4	Count of MPN's
2	;ABC123;	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	new source needed 14/12/2021	1
3	;XYZ789;	Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex	1
3	;501304;	Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex	1
4	;XYZ789;	11/06/2023 herts	01/01/2020 herts			1
4	;501304;	11/06/2023 herts	01/01/2020 herts			1
5	;GHEPANJ874755;	11/06/2023 herts	allocated all stocks, now obsolete			1
5	;847834HFHF7;	11/06/2023 herts	allocated all stocks, now obsolete			1
5	;7569FT2YYT44D;	11/06/2023	allocated all stocks, now obsolete			1

Some of the challenges we have got are that there are 60 odd thousand rows and 45 of 50 columns on some of the reports that we are receiving.. and they're growing. (Hence why we are taking steps towards tidying them up!)

On the plus side, the structure of the files always remains the same:
Column A = the row number
Column B = the concatenated MPN's (always contained in that one single column)
Column C onwards contain the information
(And there's 1 header row)


Huge thanks for any help you can provide.

 

[DanteAmor]

Hi @ellison

Here you find a formula in 365 similar to your case.

textsplit to next row with the same value in parallel row

how to textsplit column B by rows while keeping the value in column A?..thank you ABCDE1NAME1APPLE, BERRY, DOG, CATEXPECTED RESULT2NAME2APPLE, BERRYNAME1APPLE3NAME3APPLE, BERRY, CATNAME1BERRY4NAME4BERRY, DOG, CATNAME1DOG5NAME5APPLE, DOG...

www.mrexcel.com

-------------------------------

Or you can use the following macro.
Set in the macro, the names of the sheets "Raw" and "Results" by your names.

Sub Duplication_data()
  Dim shR As Worksheet
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long, m As Long
  
  Set shR = Sheets("Results")
  With Sheets("Raw")
    a = .Range("A2", .Cells(.Range("B" & Rows.Count).End(3).Row, .Cells(1, Columns.Count).End(1).Column)).Value
    .Rows(1).Copy shR.Range("A1")
    ReDim c(1 To UBound(a, 1) * 100, 1 To UBound(a, 2))
  End With
  
  For i = 1 To UBound(a, 1)
    b = Split(Mid(a(i, 2), 2, Len(a(i, 2)) - 2), ";")
    For j = 0 To UBound(b)
      k = k + 1
      For m = 1 To UBound(a, 2)
        c(k, m) = a(i, m)
        If m = 2 Then c(k, m) = ";" & b(j) & ";"
        If m = UBound(a, 2) Then c(k, m) = 1
      Next
    Next
  Next
  
  shR.Range("A2").Resize(k, UBound(c, 2)).Value = c
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[ellison]

Ever such a slight glitch, but this is VERY nearly there, amazing.
The glitch is that the results for column info-4 are always "1". So rather than processing the info (which it does on the other columns), it is just returning "1".

So here are the results vs (hopefully!) target results

row	MPN-Original	Info-1	Info-2	Info-3	Info-4
2​	;ABC123;	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	1
3​	;XYZ789;	Essex	31/05/2019 herts 08/01/2023 move		1
3​	;501304;	Essex	31/05/2019 herts 08/01/2023 move		1
4​	;XYZ789;	11/06/2023 herts	01/01/2020 herts		1
4​	;501304;	11/06/2023 herts	01/01/2020 herts		1
5​	;GHEPANJ874755;	11/06/2023	allocated all stocks, now obsolete		1
5​	;847834HFHF7;	11/06/2023	allocated all stocks, now obsolete		1
5​	;7569FT2YYT44D;	11/06/2023	allocated all stocks, now obsolete		1

Target Results

row	MPN-Original	Info-1	Info-2	Info-3	Info-4
2​	;ABC123;	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	new source needed 14/12/2021
3​	;XYZ789;	Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex
3​	;501304;	Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex
4​	;XYZ789;	11/06/2023 herts	01/01/2020 herts
4​	;501304;	11/06/2023 herts	01/01/2020 herts
5​	;GHEPANJ874755;	11/06/2023	allocated all stocks, now obsolete
5​	;847834HFHF7;	11/06/2023	allocated all stocks, now obsolete
5​	;7569FT2YYT44D;	11/06/2023	allocated all stocks, now obsolete

Would you be able to un-glitch?!

 

[DanteAmor]

Count of MPN's

I thought that column was always last.

Ever such a slight glitch

It's correct , just remove this line from the macro

If m = UBound(a, 2) Then c(k, m) = 1

 

[ellison]

That's absolutely BRILLIANT, thank you so much