split BOM by reference designators

[22strider]

Hello Friends

Could anyone please help figuring out macro (VBA routine) for the following?
The original spreadsheet has bill of materials (BOM) export with columns ParentItem, ChildItem, Qty and RefDesig. Following is the sample table:
ParentItem Child Item Qty RefDesig
P12345 C12345 2 R12,R23
P12345 C45678 3 C56,C45,C89
P12345 C6598 4

Entries under column RefDesig are separated by comma.
I need a macro that would read and count entries under RefDesig and enter rows one each for RefDesig and change qty from its original number to 1. And if there is no RefDesig the Qty will remain as is.
Following is the table showing expected output:
ParentItem Child Item Qty RefDesig
P12345 C12345 1 R12
P12345 C12345 1 R23
P12345 C45678 1 C56
P12345 C45678 1 C45
P12345 C45678 1 C89
P12345 C6598 4

You may notice that the row one (in the original table) has been split into two rows (because in original table it had 2 reference designators) and the row two has been split into three rows. And row three in the original table remains as is in the result table because there was no reference designator.
Thanks
Rajesh

 

[ffgg123]

Hi,
this is the link: https://www.dropbox.com/s/26aqbkl3vac4yqf/BOM.xlsx?dl=0
I wrote before what I like.
Thanks a lot

 

[hiker95]

Hi,
this is the link: https://www.dropbox.com/s/26aqbkl3vac4yqf/BOM.xlsx?dl=0
I wrote before what I like.
Thanks a lot

ffgg123,

Where should the results be written to?

1. In the same raw data worksheet?

1a. Beginning in what column?


2. Or, in another worksheet, and, if so, what will be the results worksheet name?


3. Will the results worksheet already exist?

3a. Or, will it have to be created?

 

[ffgg123]

Hi,
thanks for your reply.
The result can be written in the same worksheet or other for me is the same; beginning in column A.
Thanks

 

[hiker95]

ffgg123,

Here is a macro solution for you to consider, that will run in the active worksheet, that is based on your flat text displays.

Save your new workbook in a copy of your original workbook, as a Macro Enabled Workbook, with the file extension of .XLSM

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

To run the macro use the ALT+F8 keys to display the Macro list. Select the macro "?????" and click "OK".
Press the keys ALT+Q to exit the Editor, and return to Excel.

Sub SplitBOM()
' hiker95, 12/12/2018, ME613198
Dim s, maxs As Long, r As Long, lr As Long, c As Long, lc As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  lc = .UsedRange.Columns.Count
  For r = lr To 2 Step -1
    If InStr(.Cells(r, 2), ",") Then
      s = Split(.Cells(r, 2), ",")
      maxs = UBound(s)
      .Rows(r + 1).Resize(maxs).Insert
      With .Cells(r, 2).Resize(maxs + 1)
        .Value = Application.Transpose(s)
      End With
      .Cells(r, 1).Resize(maxs + 1) = .Cells(r, 1)
      For c = 3 To lc Step 1
        .Cells(r, c).Resize(maxs + 1) = .Cells(r, c)
      Next c
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

 

[ffgg123]

Hi,
i used in this file
https://www.dropbox.com/s/q2qwduclqh33ynp/BOM TEST.xlsm?dl=0
the macro wrote and posted here #15 from Iblanco.
It's fantastic for my necessity but I don't understand why the order function don't work.
The C4 is not right order.
Can you help me to understand why.

 

[hiker95]

Hi,
i used in this file
https://www.dropbox.com/s/q2qwduclqh33ynp/BOM TEST.xlsm?dl=0
the macro wrote and posted here #15 from Iblanco.
It's fantastic for my necessity but I don't understand why the order function don't work.
The C4 is not right order.
Can you help me to understand why.

I have had problems in the past when attempting to download a zipped file, or, an Excel file with macros, with the xlsm file extension.

ffgg123, if you are using the macro from Iblanco, then you will have to contact Iblanco?????

 

[hiker95]

ffgg123,

Have you tried my macro in reply #24?

 

[ffgg123]

Hi,
thanks a lot for your macro in reply #24 ; work very well.

I try also to use the macro wrote and posted here #15 from IblancoI but the order is not right.
Regards.
Merry Christmas to you.

 

[hiker95]

Hi,
thanks a lot for your macro in reply #24 ; work very well.

I try also to use the macro wrote and posted here #15 from IblancoI but the order is not right.
Regards.
Merry Christmas to you.

ffgg123,

Thanks for the feedback. You are very welcome, and, come back anytime.

And, Merry Christmas to you, also.

 

[ncrcnbl]

so, after the range is resized, you continue to use the range that was resized as a source, curious how you knew how to do that but thank you, I'll look out for that.