Help with tricky formulas

[Nic Walmsley]

Hi there,

I need some help with some tricky excel formulas. Would greatly appreciate some help as I have nearly 7000 rows of data to sort out. The developer I was hoping to get help from has handed back to me and it's beyond what I can do.

The first 2 columns below are example of my source data. I need to populate the next 3 columns with formulas. I've populated some of them manually to help explain.

The 3rd column is easy, a copy of the 1st column. Lets call these the NODE values.

The 4th column is the PARENT of the node. To find the parent, I need to look into column 2, the node level. Lets say the node I'm looking at is from level 3. The parent of this node will be the first level 2 node that occurs above it in the list. For example, 1NTH is a level 3 node, and the first level 2 node that occurs above it is SP1 - making SP1 the parent of 1NTH.

The 5th column is the PROCEEDING node from the same level. Similar to above, I need to look at the node immediately above the current node, and if it is from the same level as the current node, it is deemed the "proceeding node". Otherwise this cell is blank.

I sure hope I have explained this sufficiently, and thanks to anyone who tries to sort this one out!

Site Value	Level	Node	Parent (the first node with a lower level value above it, blank if none)	Prior Node (= the node immediately above it, if they have the same level - otherwise blank)
HOMEN	1	HOMEN
SP1	2	SP1	HOMEN
1NTH	3	1NTH	SP1
1NTHMAN	4	1NTHMAN	1NTH
1NTHMAN024	5	1NTHMAN024	1NTHMAN
1NTHMAN027	5	1NTHMAN027	1NTHMAN	1NTHMAN024
1110270641	6	1110270641	1NTHMAN027
1NTHMAN028	5	1NTHMAN028	1NTHMAN
1110280641	6	1110280641	1NTHMAN028
1NTHMAN029	5	1NTHMAN029	1NTHMAN
1NTHMAN036	5	1NTHMAN036	1NTHMAN	1NTHMAN029
1110360641	6	1110360641	1NTHMAN036
1110360642	6	1110360642	????	????
1110360831	6	1110360831	????	????
1110361605	6	1110361605	????	????
1NTHMAN038	5	1NTHMAN038	????	????
1110380641	6	1110380641	????	????
1110380642	6	1110380642	????	????
1NTHMAN043	5	1NTHMAN043	????	????
1110430641	6	1110430641	????	????
1NTHMAN045	5	1NTHMAN045	????	????
1NTHMAN047	5	1NTHMAN047	????	????
1NTHMAN048	5	1NTHMAN048	????	????
1NTHMAN049	5	1NTHMAN049	????	????
1NTHMAN050	6	1NTHMAN050	????	????
1NTHMAN051	5	1NTHMAN051	????	????
1NTHMAN052	6	1NTHMAN052	????	????
1NTHMAN053	6	1NTHMAN053	????	????
1NTHMAN054	6	1NTHMAN054	????	????
1NTHMAN055	6	1NTHMAN055	????	????
1NTHMAN056	6	1NTHMAN056	????	????
1NTHMAN057	6	1NTHMAN057	????	????
1NTHMAN058	5	1NTHMAN058	????	????
1NTHMAN059	5	1NTHMAN059	????	????
1NTHMAN060	5	1NTHMAN060	????	????
1NTHMAN061	6	1NTHMAN061	????	????
1NTHMAN062	6	1NTHMAN062	????	????
1NTHMAN063	5	1NTHMAN063	????	????
1NTHMAN064	5	1NTHMAN064	????	????
1NTHMAN065	5	1NTHMAN065	????	????
1NTHMAN066	5	1NTHMAN066	????	????
1NTHMAN067	5	1NTHMAN067	????	????
1NTHMAN068	5	1NTHMAN068	????	????
1NTHMAN069	5	1NTHMAN069	????	????
1NTHMAN070	5	1NTHMAN070	????	????
1NTHMAN071	5	1NTHMAN071	????	????
1NTHMAN072	6	1NTHMAN072	????	????
1NTHMAN073	6	1NTHMAN073	????	????
1NTHMAN074	5	1NTHMAN074	????	????
1NTHMAN075	5	1NTHMAN075	????	????
1NTHMAN076	6	1NTHMAN076	????	????
1NTHMAN077	6	1NTHMAN077	????	????
1NTHMAN078	5	1NTHMAN078	????	????
1NTHMAN079	5	1NTHMAN079	????	????
1NTHMAN080	6	1NTHMAN080	????	????
1NTHMAN081	5	1NTHMAN081	????	????
1NTHMAN082	5	1NTHMAN082	????	????
1NTHMAN083	5	1NTHMAN083	????	????
1NTHMAN084	4	1NTHMAN084	????	????
1NTHMAN085	5	1NTHMAN085	????	????
etc	etc	etc	etc	etc

[#2]

Greetings!

As understood, please find formula

for 4th column:=IF(AND(B3>B2,C2<>""),CELL("contents",C2),"")

for 5th column:=IF(AND(B3=B2,C2<>""),CELL("contents",C2),"")

Thanks,

[#3]

Hi Nic,

It could be for the row #2

Column C

=A2

Column B

=IFERROR(LOOKUP(2,1/(($B2-1)=$B$2:$B2),$A$2:$A2),"")

Column D

=IF($B2=$B1,$C1,"")

and drag them down. Attached.

[#4]

Thank you Sergei, that is much appreciated.

Your answer has helped me realize I mischaracterized the rule for Column D. I tried to rework the answer you gave for Column C (which has worked perfectly) - but couldn't get it right. Coming back here for more help is the most sensible thing to do. Hope you don't mind!

The rule for D is more like this

Look up at all the values above and find the next node with the same level, BUT if you encounter a node with a lower level number first, leave the cell blank.

So if the row I'm on is for a node at level 3, it would look up and find the next node from level 3 (looking upwards from my row towards the top). But if it hit a node from level 2 (or 1) before it found a another 3, it would leave it blank.

D could be characterized as "proceeding sibling"

Here's a new example if that helps:

Node	Level	Parent	Proceeding Sibling
Food	1
Friut	2	Food
Apple	3	Fruit
Orange	3	Fruit	Apple
Banana	3	Fruit	Orange
Grape	3	Fruit	Banana
Red Grape	4	Grape
Green Grape	4	Grape	Red Grape
Meat	2	Food	Fruit
Beef	3	Meat
Chicken	3	Meat	Beef
Lamb	3	Meat	Chicken
Fish	3	Meat	Lamb
Snapper	4	Fish
Cod	4	Fish	Snapper
Desert	2	Food	Meat
Veges	2	Food	Desert
Potatoe	3	Veges
Carrot	3	Veges	Potatoe

Once again, thank you for the help. Can't say how nice it is to find part of the internet that is friendly and helpful!

[#5 · Accepted solution]

Hi Nic,

Formula in D could be

=IF(COUNTIFS($B$1:$B1,$B2,$C$1:$C1,$C2),LOOKUP(2,1/($B2=$B$1:$B1),$A$1:$A1),"")

and attached

[#6]

thanks again Sergei, yes this formula worked. In fact it help find a missing level 4 record in the source file we had to process, out of 7000 rows of data. Much appreciated.

[#7]

Nic, so far so good, you are welcome