Navigating Indentured BOMs (Parts Lists) in Excel

[Tom]

All,

I'll apologize in advance for the long post--please bear with me on
the explanation of what I'm trying to do. My work requires that I
spend a fair amount of time wading through very large Excel indentured
bills of materials ("BOMs"). They are "indentured" because one column
contains the hierarchical level designator (1 = the top level, 2 =
children of the top level, etc.). These files are generally
structured like the following simplified example. I've inserted a " -
" to represent divisions between Excel columns. P/N = Part Number.

LEVEL - P/N - PARENT P/N

1 - 100 - TOP
2 - 101 - 100
3 - 106 - 101
3 - 110 - 101
3 - 111 - 101
2 - 102 - 100
3 - 108 - 102
2 - 103 - 100
3 - 104 - 103
4 - 109 - 104
5 - 112 - 109
etc., etc.

My particular BOM files go on for thousands of rows and up to 12
indenture levels deep. I'd like to create a series of Excel VBA
routines and a toolbar add-in for navigating these kinds of files.

Features would include:

Task 1: Find first level children of a P/N
a. Determine the indenture level of the parent P/N
b. Determine the row number of the parent P/N
c. Bracket the potential rows by determining the row number of the
next P/N at the same indenture level as the parent P/N
d. Determine the row numbers of children at the parent indenture
level plus one, subject to the row range constraints from (c)
e. Hide all rows except for the heading row, the parent row, and
child rows

Task 2: Find all children (grandchildren, etc.) of a P/N
a. Same as (1) except show all rows between the parent and the
next parent sibling row

Task 3: Find the parent of a P/N
a. Determine the indenture level of the child P/N
b. Determine the row number of the child P/N
c. Find the next smaller row number where the indenture is equal
to the child indenture less one
d. Hide all rows except the heading row, the parent row, and
possibly the child row

Task 4: Find siblings of a P/N
a. Determine the indenture level of the P/N of interest
b. Determine the row number of the P/N of interest
c. Follow the logic of (3) to find the parent, then
d. Follow the logic of (1) to find children of the parent, which
include the P/N of interest and all its siblings

Task 5: Build an ancestor list for a P/N
a. Follow the logic of (3) as many times as it takes to reach
indenture level 1
b. Hide all rows except those in the P/N of interest’s upline

Has anyone seen this done already? If not, can you offer any advice
on common VBA routines to isolate rows based on criteria in this
manner? I once knew Excel VBA fairly well, but that was nearly ten
years ago.

Best regards,

Tom

 

[Jim Cone]

Tom,
Seven years ago or so at a major electronics manufacturer, I developed
Excel VBA code to layout/massage Bom downloads that came from SAP.
It was a considerable investment of time but allowed high paid engineering
talent to view Boms in seconds. It had required hours.

The SAP output was very similar to the structure you outline and the
Boms could have 7 or 8 separate formats/layouts (identified by a report code)

From what I remember, to do the coding required a painstaking review of
each Bom structure in order to identify changes or flags in the report layout.
The code would find these flags, identify them and proceed accordingly.
It involved loops down columns with loops across columns when flags
were found. You will not have a simple job.

Your IT folks who generate this stuff may have more options/capabilities
then they have displayed to you. So first see if they can improve/simplify the
Bom layouts delivered to you. Also, understand that they can and do make
surprise "improvements" that can screw up all of your hard work.
(I think they all moved on to Microsoft) <g>

Unless you are really familiar with VBA Excel coding you may want to
hire someone to do the job.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



All,

I'll apologize in advance for the long post--please bear with me on
the explanation of what I'm trying to do. My work requires that I
spend a fair amount of time wading through very large Excel indentured
bills of materials ("BOMs"). They are "indentured" because one column
contains the hierarchical level designator (1 = the top level, 2 =
children of the top level, etc.). These files are generally
structured like the following simplified example. I've inserted a " -
" to represent divisions between Excel columns. P/N = Part Number.

LEVEL - P/N - PARENT P/N

1 - 100 - TOP
2 - 101 - 100
3 - 106 - 101
3 - 110 - 101
3 - 111 - 101
2 - 102 - 100
3 - 108 - 102
2 - 103 - 100
3 - 104 - 103
4 - 109 - 104
5 - 112 - 109
etc., etc.

My particular BOM files go on for thousands of rows and up to 12
indenture levels deep. I'd like to create a series of Excel VBA
routines and a toolbar add-in for navigating these kinds of files.

Features would include:

Task 1: Find first level children of a P/N
a. Determine the indenture level of the parent P/N
b. Determine the row number of the parent P/N
c. Bracket the potential rows by determining the row number of the
next P/N at the same indenture level as the parent P/N
d. Determine the row numbers of children at the parent indenture
level plus one, subject to the row range constraints from (c)
e. Hide all rows except for the heading row, the parent row, and
child rows

Task 2: Find all children (grandchildren, etc.) of a P/N
a. Same as (1) except show all rows between the parent and the
next parent sibling row

Task 3: Find the parent of a P/N
a. Determine the indenture level of the child P/N
b. Determine the row number of the child P/N
c. Find the next smaller row number where the indenture is equal
to the child indenture less one
d. Hide all rows except the heading row, the parent row, and
possibly the child row

Task 4: Find siblings of a P/N
a. Determine the indenture level of the P/N of interest
b. Determine the row number of the P/N of interest
c. Follow the logic of (3) to find the parent, then
d. Follow the logic of (1) to find children of the parent, which
include the P/N of interest and all its siblings

Task 5: Build an ancestor list for a P/N
a. Follow the logic of (3) as many times as it takes to reach
indenture level 1
b. Hide all rows except those in the P/N of interest’s upline

Has anyone seen this done already? If not, can you offer any advice
on common VBA routines to isolate rows based on criteria in this
manner? I once knew Excel VBA fairly well, but that was nearly ten
years ago.

Best regards,

Tom