I have a data set of members and their health condition, and every health condition has a value associated with them and also every health condition falls in a group. in the group there is a hierarchy for example group A have (8,9,10,11,12).in this group the highest in hierarchy is 8. All these numbers have values assigned to them for example 8=0.71 9=0.61 and so on. and if 8 is captured first, then it should take the value assigned to 8 which is 0.71 if 8 (or any other condition) is captured later then it should only take the difference of both of them. for example, if 9 is captured first and then 8 is captured then it will be 0.71-0.61=0.10 value. [enter image description here][1] [1]: https://i.stack.imgur.com/kg5YD.png
in the picture, on scenario1, you can see that if a health condition '10' is captured on 1/15 then the column F will take the full value of the Health condition. next on 1/16 another health condition is captured and falls in same disease group then it takes the difference of the both values. (0.65-0.55=0.1)
in scenario 2, you can see that for same member same health condition,10, is captured in 02/18 so this time the column F will show only zero because this condition was already captured
please help.
thanks