+ Reply to Thread
Results 1 to 13 of 13

Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

  1. #1
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Hi All,

    I am a little rusty with the VBA programming, but I think a macro be the only solution to my problem (list is 70,000 lines long). I am looking for general ideas on how to get started? If anyone has functions, great, but there may be other ways to do this that I can't think of right now.

    I have created macros for indented BoM's in the past, but the problem I am running into is this:

    -There is no designated top level assembly
    -Therefore, I have no idea when a component reaches the top level of the assembly. (would this be a repetitive search from 'component -> Assy -> component -> assy' until there is no found, which assumes the top level?)
    -Finally, multiple components go into multiple levels of assembly.

    Here is a rough estimate of the data I have:

    Assy Component Sequence
    Item A Item B 10
    Item A Item E 20
    Item A Item F 30
    Item A Item G 40
    Item B Item H 10
    Item C Item A 10
    Item C Item I 20
    Item C Item J 30
    Item C Item K 70
    Item C Item L 80
    Item C Item M 90
    Item D Item N 10
    Item D Item O 20
    Item D Item P 30
    Item D Item Q 40
    Item D Item R 50
    Item D Item S 60
    Item D Item C 70
    Item D Item T 80
    Item D Item U 90
    Item D Item V 100
    Item D Item W 110
    Item D Item X 120
    Item D Item Y 130
    Item D Item Z 140
    Item D Item AA 150
    Item D Item AB 160
    Item D Item AC 170
    Item D Item AD 180
    Item D Item AE 190

    With my desired output being a standard top-down indented BoM.

    Thanks in advance for the discussion.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Hi, welcome to the forum.
    How is the data presented?
    As text file ? (csv or txt) or other?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12
    Quote Originally Posted by Keebellah View Post
    Hi, welcome to the forum.
    How is the data presented?
    As text file ? (csv or txt) or other?
    Thank you Keebellah - the data is directly exported as an .xls file.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Where will you keep the macro?

  5. #5
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Quote Originally Posted by Keebellah View Post
    Where will you keep the macro?
    It will be with a workbook with multiple sheets for current and future use.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Does this mean that all the values are in one cell per row?

  7. #7
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Quote Originally Posted by Keebellah View Post
    Does this mean that all the values are in one cell per row?
    No, sorry my copy & paste was not clear, it is 3 different cells per row -

    Assembly is in column A
    Component is in column B
    Sequence (not necessary to be sorted but may help with identifying) is in column C

    Values in COL B could also be in COL A (ie. sub-assy in COL B would have an assembly associated with it in COL A).

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    In that list, there appear to be two top assembles -- A and D. Is that correct?
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Quote Originally Posted by shg View Post
    In that list, there appear to be two top assembles -- A and D. Is that correct?
    No, D is the top level assembly.

    starting from the lowest level:

    H -> B -> A -> C -> D

    This is where I run into my problems - however, I believe this may be the place to start.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    So like this?

    B
    C
    O
    4
    Comp
    Assy
    Sort
    5
    D
    D
    6
    AA
    D
    D AA
    7
    AB
    D
    D AB
    8
    AC
    D
    D AC
    9
    AD
    D
    D AD
    10
    AE
    D
    D AE
    11
    C
    D
    D C
    12
    A
    C
    D C A
    13
    B
    A
    D C A B
    14
    H
    B
    D C A B H
    15
    E
    A
    D C A E
    16
    F
    A
    D C A F
    17
    G
    A
    D C A G
    18
    I
    C
    D C I
    19
    J
    C
    D C J
    20
    K
    C
    D C K
    21
    L
    C
    D C L
    22
    M
    C
    D C M
    23
    N
    D
    D N
    24
    O
    D
    D O
    25
    P
    D
    D P
    26
    Q
    D
    D Q
    27
    R
    D
    D R
    28
    S
    D
    D S
    29
    T
    D
    D T
    30
    U
    D
    D U
    31
    V
    D
    D V
    32
    W
    D
    D W
    33
    X
    D
    D X
    34
    Y
    D
    D Y
    35
    Z
    D
    D Z

  11. #11
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Quote Originally Posted by shg View Post
    So like this?
    Yes thank you, but how would this work if some of these components went into other assemblies?

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    That would require a different approach.This was adapted from a workbook that generates org charts, where every employee (component) has exactly one supervisor (next higher assembly). I have some BOM stuff, but won't get a chance to look until later this week. Someone else may solve it for you in the meanwhile.

  13. #13
    Registered User
    Join Date
    10-10-2017
    Location
    West Coast
    MS-Off Ver
    2016
    Posts
    12

    Re: Creating an Indented BoM from Two Columns of Assemblies/Sub-Assemblies

    Quote Originally Posted by shg View Post
    That would require a different approach.This was adapted from a workbook that generates org charts, where every employee (component) has exactly one supervisor (next higher assembly). I have some BOM stuff, but won't get a chance to look until later this week. Someone else may solve it for you in the meanwhile.
    Thank you shg, i'll keep plugging away. It almost seems like i have to do a reverse look up, rebuild, and then expand to get the BoM.

+ 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: 0
    Last Post: 08-30-2016, 01:44 PM
  2. VBA code for inventory & parts for assemblies
    By mikehell in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2015, 03:18 PM
  3. [SOLVED] numbering components within assemblies
    By taylorcharley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2015, 10:01 AM
  4. Create a list of bought-out items for various assemblies
    By aimeelou2006 in forum Excel General
    Replies: 2
    Last Post: 04-02-2013, 05:43 PM
  5. Problem with cell assemblies
    By desonny in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-09-2011, 12:22 AM
  6. bill of material, assemblies
    By jamesw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2011, 01:53 PM
  7. Updating Inventory for Assemblies
    By hpmachining in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2010, 02:47 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