I just can't seem to figure out how to create this formula. So let me try my best to explain. I have a set of data with various members where each of these members have 1 or more "HCCs." I also have a hierarchy table in which if a member has a "highest rank HCC" and a lower rank HCC in the same row, the highest rank HCC takes precedence. What I want the formula to do is output "1" if the member has a unique HCC that isn't outranked by another HCC for the same member and a "0" if it's a duplicate HCC or another HCC outranks it for the SAME member (I hope that made sense).
Sample of data Hierarchy table (if member has HCC in first column it trumps all "Low HCCs" in the same row for the same member)
<tbody>
</tbody>
Thank you.
Sample of data Hierarchy table (if member has HCC in first column it trumps all "Low HCCs" in the same row for the same member)
Member | HCC | Output Should look like | Highest Ranked HCC | Low HCC | Low HCC | Low HCC | Low HCC | Low HCC | |
1 | 47 | 1 | 8 | 9 | 10 | 11 | 12 | ||
2 | 18 | 1 | 9 | 10 | 11 | 12 | |||
2 | 18 | 0 | 10 | 11 | 12 | ||||
2 | 106 | 1 | 11 | 12 | |||||
2 | 114 | 1 | 17 | 18 | 19 | ||||
2 | 161 | 0 | 18 | 19 | |||||
3 | 111 | 1 | 27 | 28 | 29 | 80 | |||
3 | 112 | 0 | 28 | 29 | |||||
4 | 8 | 1 | 46 | 48 | |||||
4 | 8 | 0 | 54 | 55 | |||||
4 | 11 | 0 | 57 | 58 | |||||
4 | 12 | 0 | 70 | 71 | 72 | 103 | 104 | 169 | |
71 | 72 | 104 | 169 | ||||||
72 | 169 | ||||||||
82 | 83 | 84 | |||||||
83 | 84 | ||||||||
86 | 87 | 88 | |||||||
87 | 88 | ||||||||
99 | 100 | ||||||||
103 | 104 | ||||||||
106 | 107 | 108 | 161 | 189 | |||||
107 | 108 | ||||||||
110 | 111 | 112 | |||||||
111 | 112 | ||||||||
114 | 115 | ||||||||
134 | 135 | 136 | 137 | ||||||
135 | 136 | 137 | |||||||
136 | 137 | ||||||||
157 | 158 | 161 | |||||||
158 | 161 | ||||||||
166 | 80 | 167 | |||||||
<tbody>
</tbody>
Thank you.