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?