+ Reply to Thread
Results 1 to 6 of 6

Multi-Level Bill of Material Automatic Grouping

  1. #1
    Registered User
    Join Date
    06-24-2011
    Location
    Philadelphia, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Multi-Level Bill of Material Automatic Grouping

    Hi All,

    Before I go off and try to write my own macro to do this (I'm not the best of VBA programmers) I was wondering if anyone has already solved this seemingly common problem. I have a multi-level bill of material exported from a database. The first column of this data shows the level of the entry. I would like to automatically group the data as shown. I've attached a sample sheet with some simplified data showing how it should be grouped.

    The rules would be. Indexing down the rows
    1. Look at the current level, call this START_LEVEL
    2. If the level of the next row is greater then the current row level start a group
    3. Continue to put items in this group until the level specified is back to START_LEVEL, do not put this item with the level=START_LEVEL in the current group

    This would then have to be repeated again on the entries within the just created group since groups may exist within groups. I imagine doing this recursively until the next line is outside of the first group at which time you would start at the beginning of the above steps.

    Starting List.jpg
    Grouped List.jpg
    BOM Auto Level.xlsx
    Last edited by mwasserman; 03-02-2014 at 06:47 PM. Reason: updated an image

  2. #2
    Registered User
    Join Date
    06-24-2011
    Location
    Philadelphia, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Multi-Level Bill of Material Automatic Grouping

    I was able to answer my own question and learned a bit while doing it. My code if anyone else needs it

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-23-2009
    Location
    Milwaukee, WI
    MS-Off Ver
    Excel:Mac 2008
    Posts
    4

    Re: Multi-Level Bill of Material Automatic Grouping

    I'm Grave Digging here -

    I put the code above into use. But I have a BOM that is more than 12 levels deep.

    Excel limits the grouping down to 8 levels - so at the 9th level the Macro errors out.

    What modifications do I need to make to combine anything at levels 9-12? I need to preserve the BOM structure, so I cant just delete them, or change the level to 8.

    Thanks,
    Max

  4. #4
    Registered User
    Join Date
    06-17-2015
    Location
    Santa Rosa, USA
    MS-Off Ver
    2013
    Posts
    1

    Re: Multi-Level Bill of Material Automatic Grouping

    For i = StartRow To LastRow
    CurrentLevel = Cells(i, LevelCol)


    If CurrentLevel > 8 Then
    CurrentLevel = 8
    End If

    Rows(i).Select
    For j = 1 To CurrentLevel - 1
    Selection.Rows.Group
    Next j
    Next i

  5. #5
    Registered User
    Join Date
    08-10-2015
    Location
    Taiwan
    MS-Off Ver
    MS Office 2013
    Posts
    1

    Re: Multi-Level Bill of Material Automatic Grouping

    Hi mwasserman,

    thanks for your code. Helped alot!
    Last edited by carlbbq; 08-11-2015 at 04:32 AM.

  6. #6
    Registered User
    Join Date
    06-21-2021
    Location
    Baltimore
    MS-Off Ver
    MS 360
    Posts
    1

    Re: Multi-Level Bill of Material Automatic Grouping

    Thank you. This code helped me on a tight schedule.

    sferguss

+ 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. [SOLVED] VBA for calculating total quantity in multi level Bill of material
    By redmarko in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-29-2020, 05:45 PM
  2. Multi level Bill of material
    By neorez in forum Excel General
    Replies: 36
    Last Post: 03-09-2016, 02:05 AM
  3. 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
  4. bill of material, assemblies
    By jamesw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2011, 01:53 PM
  5. Multi level Bill of material
    By neorez in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-21-2011, 04:35 AM

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