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
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.
Nic, so far so good, you are welcome