Searching "Up" an Array

[mark.wolven]

I have an Org Tree type spreadsheet. Looking at it, it is easy to see whichcell contains the information about it's parent. But I would like to be able to extract a set of Parent/Child Pairs. I have an Index/Match that returns the column header, so, I can see the level of each node.

However, a node may be in row 100, at Level 4 (4 levels deep in the hierarchy). It's parent should be the nearest Level 3 node in rows 1-99. And by nearest, I mean the highest row, closest to row 100.

Is there any way to do that within a function?

 

[isabelle]

hi Mark,

can you describe the disposal of data in more detail ?
or post an address so that we can see the file.

isabelle

Le 2013-02-13 17:14, (e-mail address removed) a écrit :

I have an Org Tree type spreadsheet. Looking at it, it is easy to see which cell contains the information about it's parent.

But I would like to be able to extract a set of Parent/Child Pairs. I
have an Index/Match that returns the column header, so, I can see the
level of each node.

However, a node may be in row 100, at Level 4 (4 levels deep in the hierarchy).

It's parent should be the nearest Level 3 node in rows 1-99. And by
nearest, I mean the highest row, closest to row 100.

 

[mark.wolven]

hi Mark, can you describe the disposal of data in more detail ? or post an address so that we can see the file. isabelle Le 2013-02-13 17:14, (e-mail address removed) a écrit : > I have an Org Tree type spreadsheet. Looking at it, it is easy to see which cell contains the information about it's parent. But I would like to be able to extract a set of Parent/Child Pairs. I have an Index/Match that returns the column header, so, I can see the levelof each node. > > However, a node may be in row 100, at Level 4 (4 levels deep in the hierarchy). It's parent should be the nearest Level 3 node in rows 1-99. And by nearest, I mean the highest row, closest to row 100. > > Is there any way to do that within a function? >

Level 1 --|-----Level 2 --|-----Level 3---|
B146900001 ----------| ----------|
----------| B146900031 ----------|
----------| ----------| B146900032
----------| ----------| B146900033
----------| ----------| B146900034
B146900002 ----------| ----------|
----------| B146900037 ----------|
----------| B146900003 ----------|
----------| ----------| B146900004
----------| ----------| B146900005
----------| B146900006 ----------|
----------| B146900044 ----------|
----------| B146900007 ----------|
----------| ----------| B146900045
----------| ----------| B146900046
----------| ----------| B146900047

Here's a small sample. For example, if I searched for B146900047, I want toknow that it's parent is B146900007.

And if I search for B146900007, I want to know that it's parent is B146900002.

I need to search 1 column to the left and find the value with the nearest row number. Does that help?

 

[GS]

Have a look at the Offset() function. In your case you want to offset
from the Find range 1 col to the left, then search up that col for the
first non-blank. Something like...

<aircode>
Set rng = Find(...) '//do the search
If Not rng Is Nothing Then '//if found
sParent = rng.Offset(0, -1).End(xlUp).Value
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion