Please help me with a search function for a column. Given data in the
Level and Part columns below, I am trying to populate the Parent
column with the parent part number for each Part in a row. The
parent/child relationship is designated by the Level column entry,
where, for example, all of the Level 2 parts under a given Level 1
part have that Level 1 part as a parent. With nested IF functions, I
can easily march down the data as long as the Level number is
increasing or the Level returns to a value of 1. But as shown by the
"???" in the Parent column below, I do not have a way to easily and
systematically note that the parent for the Level 2 Part 090-806-00 is
really the first Level 1 part above that, Part 032-263-00. There is no
way to know ahead of time how many part of levels might be between a
part and its parent. Any suggestions?


Parent Level Part
(Top Level) 0 032-102-00
032-102-00 1 032-263-00
032-263-00 2 C
032-263-00 2 033-151-00
033-151-00 3 28D33151
??? 2 090-806-00
??? 2 091-449-00
??? 2 091-965-00
??? 2 091-966-00
??? 2 090-221-00
??? 2 470-163-13
??? 2 470-023-67
??? 2 470-207-49
??? 2 28C32263
032-102-00 1 094-018-00

You have to use nested loops, that way every cell in one column looks at every cell in the next column
I'm just giving you the strategy. I'm asssuming you can do the mecahanics
( a more efficient version will actually reset the range by one less row on each pass so your not re-comparing the same cells over but thats a bit more complicated . this should work fine.)
Note: this assumes your data is contiguous, no blank rows till the end
dim Rng1 as Range, Rng2 as Range, C1 as Range, C2 Rang

Set Rng1 = Columns(1
Set Rng2 =Columns(2

For Each C1 in Rng
If C1="" Then Exit Fo

For Each C2 in Rng
If C2="" Then Exit Fo
if c2 = c1 the
do sometthin
end i
Next C

Next C


----- KC wrote: ----

Please help me with a search function for a column. Given data in th
Level and Part columns below, I am trying to populate the Paren
column with the parent part number for each Part in a row. Th
parent/child relationship is designated by the Level column entry
where, for example, all of the Level 2 parts under a given Level
part have that Level 1 part as a parent. With nested IF functions,
can easily march down the data as long as the Level number i
increasing or the Level returns to a value of 1. But as shown by th
"???" in the Parent column below, I do not have a way to easily an
systematically note that the parent for the Level 2 Part 090-806-00 i
really the first Level 1 part above that, Part 032-263-00. There is n
way to know ahead of time how many part of levels might be between
part and its parent. Any suggestions


Parent Level Par
(Top Level) 0 032-102-0
032-102-00 1 032-263-0
032-263-00 2
032-263-00 2 033-151-0
033-151-00 3 28D3315
??? 2 090-806-0
??? 2 091-449-0
??? 2 091-965-0
??? 2 091-966-0
??? 2 090-221-0
??? 2 470-163-1
??? 2 470-023-6
??? 2 470-207-4
??? 2 28C3226
032-102-00 1 094-018-0

Hi
if your parent is col. A, level col. B and the description col. C and
the data starts in row 2 (row 1 is a heading row) then enter the
following array formula in A3 (A2 is the top level). Enter this formula
with CTRL+sHIFT+ENTER:
=INDEX($C$1:$C$100,MAX(IF($B$2:$B2=B3-1,ROW($B$2:$B2))))

copy this formula down for all rows.

--
Regards
Frank Kabel
Frankfurt, Germany


KC wrote:
Please help me with a search function for a column. Given data in the
Level and Part columns below, I am trying to populate the Parent
column with the parent part number for each Part in a row. The
parent/child relationship is designated by the Level column entry,
where, for example, all of the Level 2 parts under a given Level 1
part have that Level 1 part as a parent. With nested IF functions, I
can easily march down the data as long as the Level number is
increasing or the Level returns to a value of 1. But as shown by the
"???" in the Parent column below, I do not have a way to easily and
systematically note that the parent for the Level 2 Part 090-806-00
is
really the first Level 1 part above that, Part 032-263-00. There is
no
way to know ahead of time how many part of levels might be between a
part and its parent. Any suggestions?


Parent Level Part
(Top Level) 0 032-102-00
032-102-00 1 032-263-00
032-263-00 2 C
032-263-00 2 033-151-00
033-151-00 3 28D33151
??? 2 090-806-00
??? 2 091-449-00
??? 2 091-965-00
??? 2 091-966-00
??? 2 090-221-00
??? 2 470-163-13
??? 2 470-023-67
??? 2 470-207-49
??? 2 28C32263
032-102-00 1 094-018-00

Thanks for the suggestion, but I cannot get it to work. Can I send a
file except to try?

Hi
go ahead:
frank[dot]kabel[at]freenet[dot]de

But prior to this: What is the problem you encounter (note: you have to
enter this as array formula)

--
Regards
Frank Kabel
Frankfurt, Germany


KC wrote:
Thanks for the suggestion, but I cannot get it to work. Can I send a
file except to try?