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