Hi all,

I have eight data-sets (between 8,000-10,000 records each) that were
very clearly not setup by anyone who cared that people might one day
actually want to use the data.

The basic format is (at least with a fixed-width font):

Row Level Code Description
--- ----- ---- --------------
1 1 A001 Group_01
2 2 0570 Subgroup_01
3 3 0001 Widget_01
4 3 0002 Widget_02
5 3 0003 Widget_03
6 2 0598 Subgroup_01
7 3 0782 Subsubgroup_01
8 4 0001 Widget_11
9 4 0002 Widget_12
10 5 0001 Subwidget_121
11 5 0002 Subwidget_122
12 3 6387 Subsubgroup_02
13 3 0598 Subsubgroup_03
14 4 0001 Widget_01
15 4 0002 Widget_02
16 4 0003 Widget_03
17 2 0323 Subgroup_02

I hope this rather generic data-set captures what I'm trying to
demonstrate. Basically, the level and code fields are the key. The
data-set is hierarchical, but the data-tables don't make analysis easy.
The level indicates branches of a tree, similar to a file system
directory structure. Just imagine that it's machines, systems,
subsystems, and parts rather than drives, folders, sub-folders, and
files.

I have a list of codes that are considered critical. Unfortunately,
it's not a simple vlookup or index-match, but rather extracting all the
sub-records (identified by the level field) if I find a record with a
code that matches up to a code on my list.

To demonstrate using the data above, say codes 0782 and 0598 are on my
list of critical codes. For 0782, I need to extract or flag rows 7
through 11. For 0598, I need to extract of flag rows 13 through 16.

Furthermore, the data is such that the codes are not always unique and
are not always found on the same level. For instance, the group of
parts identified by 0782 could be at level 3 as in the example above,
but at level 5 elsewhere in the data. The sub-parts could also be
different even though they are both identified as 0782.

It gets further complicated in that the code isn't always a code, in
the example above, the 0001+ codes just indicate a sequence of parts,
their part numbers are identified by another field in the record.

Ideally, I'd like to make two modifications to the data. Initially, if
I find a record that has a critical code, I'd like to add an "x" to a
check field at the end of the record. Secondly, I'd like to separate
the group by inserting a row above and below the identified group.

The process, as I envision it, goes something like this:

Starting from record (row) 1, search the code field for a code that
matches my critical codes list (contained in another Excel file, only
about 140 records). When a record is identified, insert a row above
the record, place an "x" in the check field at the end of the record
and continue placing an "x" in the check field for all subsequent
records as long as the level (number) is greater than the level of the
record with the matching code. Once a record with an equal or lower
(numbered) level is found, insert a row to block off the chunk of data
and continue the process throughout the data-set.

I'm very much at a loss. I've been out of the country traveling for
fun for the past seven months and I've been away from Excel for almost
a year. I know there's a way to do this, it's just not coming to me.
Can anyone offer any assistance, it would be greatly appreciated.

Cheers,

Matt