+ Reply to Thread
Results 1 to 4 of 4

Need Excel Formula for Indented Bill Of Materials

  1. #1
    Registered User
    Join Date
    10-03-2011
    Location
    miami fl
    MS-Off Ver
    Excel 2010
    Posts
    1

    Exclamation Need Excel Formula for Indented Bill Of Materials

    Hello Guys, I have this Project in which I am supposed to convert this Indented BOM (Bill of materials) and convert it to a desired output (please see the attached file) at first glance I believed it was an easy problem, however when dealing with indented bills the item will be at a certain level and the quantity of the item will depend on that. Take an example (on the attached sheet)

    Bolt1 is three times on the list,
    - The first one on level 2 (inside of subassy 1, subassy 1 has a quantity of 2) and it has a quantity of 3; therefore we will have 3X2=6... 6 being the number of bolts1 on that level.

    -The second one is on level level 3 (inside subassi3, which is inside of subassy2), and it has a quantity of 4, since bolt one is inside of "subassy3",4 quantities, and "subassy3" is inside "subassy2", 2 quantities. therefore we will have 4X4=16 (now at level three we go up the level and; 16X2=32... 32 being the number of bolts on that level.

    - the third Bolt1 is located on level 1 and there is 1 quantity, since is on level on 1x1=1 having only 1 bolt.

    Finally adding up all "bolt1" we would have 6+32+1=39 Bolt1.

    I will need to do that for all the parts listed on the indented bill, i have tried using the vlookup function and it will return to me the number, however i dont know how to implement the indented level of the bill.
    If someone could help me I will greaatly appreciate it.

    Thanks!
    Attached Files Attached Files
    Last edited by stalinc434; 10-04-2011 at 10:56 PM. Reason: Moderator

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Need Help Please Solving This Problem

    Hi stalinc434,

    The workbook sample you have uploaded is incomplete, since it seems to be linked to other workbook/s making it impossible to exactly analyse and or assist you with your problem.

    An "Indented" B.O.M. also reffered to as a Multi Level B.O.M. is very difficult to create in excel ! It would require careful planning of the basic outlay, to accommodate any possible changes with regard to adding or deleting from it.
    Last edited by Winon; 10-05-2011 at 08:13 AM. Reason: Edit content of text

  3. #3
    Registered User
    Join Date
    08-18-2021
    Location
    USA
    MS-Off Ver
    20H2
    Posts
    1

    Re: Need Help Please Solving This Problem

    Hi,

    I have an intended BOM from solid works and I would like to create an indented tree view. right now the raw indented excel/CSV/text files have random numbers. Please help me to get some templates.

    Attachment 1 Raw Indented BOM from solid works. Link Raw Indented data from Solid works.PNG
    Attachment 2 is an example of the output. Link BOM Indented tree 1.pngBOM Indented tree.jpg

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need Help Please Solving This Problem

    Quote Originally Posted by tpulagura View Post
    Hi,

    I have an intended BOM from solid works and I would like to create an indented tree view. right now the raw indented excel/CSV/text files have random numbers. Please help me to get some templates.
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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