Forum Discussion
Proceeding sibling
Hi there
I'm looking for some help with an excel formula. Have tried myself but is beyond my capabilities.
In the table below, I need a formula for Column D. If I were to characterize the rule for this column, it would be:
Look up at all the Node values above the current row, and find the next node with the same level as the current row, BUT if you encounter a node with a lower level number first, leave the cell blank.
So for example, 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 a lower level (2 or 1 in this example) before it found another level 3, it would leave Column D blank.
D could be characterized as "proceeding sibling"
Here's an example of how it should look:
| 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 |
| Potato | 3 | Veges | |
| Carrot | 3 | Veges | Potato |
It looks like duplicated post
https://techcommunity.microsoft.com/t5/Excel/Help-with-tricky-formulas/m-p/224608#M16794
I think i can do it, but only with a couple of helper columns. I've attached a workbook that does what you wanted..
The helper cells do the following.
Row Number: an array formula that returns the largest row number that the current node apears in above the current row
If statement: uses the row number to check that the smallest number between whatever row we found above and the row 1 above the row we are on now isn't smaller than the current node
I cant combine them in to one huge, probably impossible to edit formula as the if statement brakes if its entered as an array.
Hopefully that helps.
thanks!
