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