+ Reply to Thread
Results 1 to 36 of 36

Macro needed to convert multi-level BOM to single-level BOM as per attached file

  1. #1
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Post Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Dear all,

    I'm seeking for macro to convert Multi-level BOM (Bill of Material)to single-level one as per attached file..

    I always do that manually but for big projects it is exhausting

    I'm getting the multi-level BOM from Autodesk Inventor and in-order to export it to Odoo ERP system I have to convert it as per file..

    thanks in advance for your help..

    Ahmed Saad
    Attached Files Attached Files
    Last edited by AhmedSaad; 05-06-2021 at 06:05 PM. Reason: adding new file with input sheet and desired outpu sheet

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Hi Ahmed & welcome to the forum,

    Although you've provided a sample file, you haven't clearly indicated what is the before setup/structure & the after. I suggest that you have sheet1 of what you currently have & sheet2 as expected result after running the macro

  3. #3
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    I did the changes.. the first sheet is input and the second sheet is the desired result output (changing multi-level BOM to Single level one)

    thanks for your care and support

    Ahmed Saad

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Ahmed,

    Try below macro based on your sample file ...
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  5. #5
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Thumbs up Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    First of all thank you very much for your contribution to solve my problem... I really appreciate that...

    the macro worked perfectly with the same inputs i sent to you, but when i extended the parts with addition of more assemblies with more levels the result was not correct regarding the new inputs..

    please check the new added Boms with light color blue shaded cell and see how it effects the result...

    i think that the code should cover much more products with much more levels ( may be up to 6 levels)

    with you a very nice day... and agian thanks for your effort

    Ahmed Saad

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    In the original file, the numbers were separated by a dot "." whereas the new file some of them have a separator as comma "," ... Is this correct or a typo & it should be a dot ? Also, can you show the expected results after the new added lines

  7. #7
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Cool Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Oh my God,

    yes it is my mistake i forgot to replace the "," for the new added groups with "." as you noticed.. really sorry

    now I replaced all the comma's and run the code again, it ok with the children, but for parents the qty's gives #N/A for many of them... i colored them in orange

    I think one step more for flawless success.... thank you

    Ahmed Saad
    Attached Files Attached Files

  8. #8
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    For "BoM Lines/Component 11.5.3.4.1 , what should the "Product" be ? It is currently showing 11.5. which I don't think is correct. Should product show only up to 2 digits ? i.e. 11.5 or should extend to 11.5.3.4 for example ?

  9. #9
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    on the input sheet there is no 11.5.

    i think the macro did that glitch

  10. #10
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    by the way if you want to change the separator to any desired one like ("-","/","_" or "," ) you can do that if needed

  11. #11
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    check also the output sheet, parent (1) it includes parts 10.1 ---10.9 and parts 11.1 --- 11.5
    this is also not correct...

    the parts 10.1----10.9 should follow parent (10)
    the parts 11.1----11.5 should follow parent (11)


    this is why i think the "." makes these mistakes (in my opinion but i'm not expert in this field )

    thanks

    Ahmed Saad

  12. #12
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Red face Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Dear nankw83 (sorry I don't know your name ),

    I have colored all the missed up cells in the macro output sheet

    I believe it will not a big deal with you...

    thanks for your effort

    Ahmed Saad
    Attached Files Attached Files

  13. #13
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Hi Ahmed,

    I have been trying different methods but haven't reached a solution It is more complex than I though when added the different levels. In additions, text sorting takes the first character/number that what distorts the order of 1.7 -> 1.8 -> 10.1 -> 10.2 ... etc.

    Hopefully someone else can chime in with a solution

  14. #14
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    oh...
    anyway thanks for your effort and trials to help me... that was enough for me...

    one final questions... up to what level the macro can work? and wil it work up to the 9th parent?

    thanks

  15. #15
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    it works perfectly up to parent 9, but starting from 6th parent it doesn't count the parents and declare #N/A

    hope you fix this one at least to work with small Bom's untill someone help to solve the rest


    by the way using separator instead "." will help in solving the 10th and 11th parents...just try it..


    regards,
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    I'm going to work with commas as separators instead of "." Because when sorting the 1.1 is same as 1.10 for computer and i think this is the reason for the mess..

    What i need is to modify the macro to work with commas.. can yoy plz help in that?

    Thanks a lot

  17. #17
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Hi Ahmed,

    First you need to replace the dots with commas in your "input" sheet & in the code you need to replace it in two locations as highlighted in red below .. But I don't think this will solve your issue

    Please Login or Register  to view this content.

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Maybe try

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 05-08-2021 at 06:02 AM. Reason: correction remove r.Select

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    My take on this one...As you do not have a full expected result?
    Sure it can be simplified...no time right now...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 05-08-2021 at 04:13 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  20. #20
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    dear Sintek,

    thanks a lot for your amazing effort...

    i tried the macro with more families and parts but some problems appeared on the result sheet.. i colored them with light blue color..

    by the way if the separator "." is making the problem (and i believe so) we can use any other number separators like ("-" or "," or whatever suitable)


    thank you very much for your help

    Ahmed Saad
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @Bo_Ry

    the macro stops without completing the routine and request dubugging..

    is there any steps i should follow to avoid that?

    thanks a lot
    Last edited by AhmedSaad; 05-08-2021 at 05:49 AM.

  22. #22
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Quote Originally Posted by AhmedSaad View Post
    the macro stops without completing the routine and request dubugging.. is there any steps i should follow to avoid that?
    Ahmed, if there are multiple users in the thread, you either quote the person you're replying to or address them in you replay like @nankw83 to make things clear

  23. #23
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Got it ... and thank you @nankw83

  24. #24
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    By the way...Your input sheet col A has different formats...
    Run button in file...Will work up to 7 dots...can increase if need be...

    And remember 8.10 has no value associated...
    Untitled.png
    Attached Files Attached Files
    Last edited by sintek; 05-08-2021 at 06:48 AM.

  25. #25
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @sintek

    please find the table with "-" separator..

    using commas as a separator is mess-up during sorting

    please update the macro to adapt the "-" separator...and i believe it will work smoothly

    thanks a lot

    Regards,

    Ahmed Saad
    Attached Files Attached Files

  26. #26
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    It works smoothly with the dot?

    Does post 24 not produce correct output...

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    AhmedSaad,

    Here's my interpretation of your problem.
    Please Login or Register  to view this content.

  28. #28
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @jindon

    thanks for your kind help and support..


    the macro worked great...but the only problem is the yellow highlighted cell for product 11 on the table (attached)

    hope it is easy to correct

    thanks a lot

    Ahmed

  29. #29
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Try


    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @Bo_Ry

    thanks a lot ... it is better one... but still have small error when the children reaches to 10th (ie: 1.10 or 8.10 ) still missing the zero..

    please check the yellow boarded cells in attached file

    thank a lot

    Ahmed

  31. #31
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Your input at A150 and A178 need to be text '1.10 and '8.10

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    See if this is how you wanted.
    Please Login or Register  to view this content.
    Last edited by jindon; 05-12-2021 at 03:18 AM. Reason: Workbook attached

  33. #33
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @jindon

    the children 8.10 and 10.10 disappeared from the table...

  34. #34
    Registered User
    Join Date
    05-04-2021
    Location
    Cairo,Egypt
    MS-Off Ver
    2010
    Posts
    19

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    @Bo_Ry

    i converted to text but gives same result... 8.1 and 10.1 instead of 8.10 and 10.10

  35. #35
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    I don't understand what you mean.
    I see 8 in row 51 and 10 in row 73 in my result.

    If it is not what you want, I need to see your EXACT result.
    Last edited by jindon; 05-12-2021 at 08:09 AM.

  36. #36
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Macro needed to convert multi-level BOM to single-level BOM as per attached file

    Quote Originally Posted by AhmedSaad View Post
    @Bo_Ry

    i converted to text but gives same result... 8.1 and 10.1 instead of 8.10 and 10.10
    Last try. I'm out

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 06-04-2020, 06:12 PM
  2. Multi level IF THEN Macro across workbooks
    By KSM Law in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2019, 06:40 PM
  3. Format the second level of multi-level category axis - clustered chart
    By mushkitoes in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-04-2018, 09:30 AM
  4. [SOLVED] creating Multi-level list styles using existing multi-level list?
    By JimmyWilliams in forum Word Formatting & General
    Replies: 2
    Last Post: 08-28-2017, 12:21 AM
  5. Bill of Materials conversion from multi level to single level
    By susmitpatel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-11-2013, 12:53 AM
  6. [SOLVED] Formula needed for multi level discounts for seperate blocks
    By neal1079 in forum Excel General
    Replies: 3
    Last Post: 07-05-2012, 04:10 PM
  7. Using macro to convert single level BOM to Multi Level BOM
    By andrew_chong in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2006, 04:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1