locked
Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA RRS feed

  • Question

  • Dear Experts

    I am having vertical Bill of Materials (BOMs) of my 1000s of items in Columnar form. In need to convert it into rowwise format BOMs.

    Following are the sample source boms available with me.

    Line No. Parent Item No_ Parent Item No_2 Stoke No_ No_ 2 Description Quantity per Unit of Measure Code
    10000 FG-000001 1280.08.10.M 10 RW-001935 RK1280/0001 End Cover & Piston x 1280.08.0.M Cylinder 1 PCS
    20000 FG-000001 1280.08.10.M 10 RW-001937 R/TAX8/9.27 AISI 303 1280 D. Dia.8 45 mm
    30000 FG-000001 1280.08.10.M 10 RW-001936 R/BX4 Cylinder Piston rod AISI 303 ¹4 91 mm
    10000 FG-000002 1280.08.15.M 15 RW-001935 RK1280/0001 End Cover & Piston x 1280.08.0.M Cylinder 1 PCS
    20000 FG-000002 1280.08.15.M 15 RW-001937 R/TAX8/9.27 AISI 303 1280 D. Dia.8 50 mm
    30000 FG-000002 1280.08.15.M 15 RW-001936 R/BX4 Cylinder Piston rod AISI 303 ¹4 96 mm

    I need to convert them in following format:-

    Parent ERP Code Parent Pnx Code Stroke Child1 ERP Code Child1 Pnx Code Child1 Description Child1 Qty Child1 Unit Child2 ERP Code Child2 Pnx Code Child2 Description Child2 Qty Child2 Unit Child3 ERP Code Child3 Pnx Code Child3 Description Child3 Qty Child3 Unit
    FG-000001 1280.08.10.M 10 RW-001935 RK1280/0001 End Cover & Piston x 1280.08.0.M Cylinder 1 PCS RW-001937 R/TAX8/9.27 AISI 303 1280 D. Dia.8 45 mm RW-001936 R/BX4 Cylinder Piston rod AISI 303 ¹4 91 mm
    FG-000002 1280.08.15.M 15 RW-001935 RK1280/0001 End Cover & Piston x 1280.08.0.M Cylinder 1 PCS RW-001937 R/TAX8/9.27 AISI 303 1280 D. Dia.8 50 mm RW-001936 R/BX4 Cylinder Piston rod AISI 303 ¹4 96 mm

    Can somebody help my achieving the above desired output?

    Thanks for your help in advance.


    Manish


    Friday, May 18, 2018 4:43 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, May 21, 2018 2:45 AM
  • Hello mpagreja,

    Your data looks a little mess in the site. I failed to understand them all. To avoid misunderstanding, cloud you please  share a simply workbook?

    You could share the workbook via cloud storage, such as cloud storage, and then put link address here.

    Thanks for understanding,

    Best  Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 21, 2018 10:01 AM
  • Thanks terry for showing your concern in this matter. This query has just been resolved. following is the link of the same.

    http://www.msofficeforums.com/excel-programming/39132-conversion-columnwise-bill-material-into-rowwise-bill.html#post128704

    Thanks again.


    Manish

    Monday, May 21, 2018 3:45 PM
  • Hello mpapreja,

    I;m glad to hear that your issue has been resolved. I would suggest you mark your solution to close the thread. It will be helpful for other developers who run into the same issue found the solution easily. 

    If you have any other issue, please feel free to post threads to let us know.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 22, 2018 2:15 AM