Forum Discussion
Help with tricky formulas
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
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.
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!
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
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.
