Excel Formula with Hierarchy Table

[ctowner]

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)
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65"]Member[/TD]
[TD="class: xl65, width: 64"]HCC[/TD]
[TD="width: 64"]Output Should look like[/TD]
[TD="width: 64"][/TD]
[TD="class: xl67, width: 64"]Highest Ranked HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[/TR]
[TR]
[TD="class: xl71"]1[/TD]
[TD="class: xl71"]47[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl69"]9[/TD]
[TD="class: xl69"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]18[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl69"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]18[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]106[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]114[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl69"]18[/TD]
[TD="class: xl69"]19[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]161[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl69"]19[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl71"]111[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl69"]28[/TD]
[TD="class: xl69"]29[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl71"]112[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl69"]29[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]8[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]46[/TD]
[TD="class: xl70"]48[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]54[/TD]
[TD="class: xl69"]55[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]11[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]57[/TD]
[TD="class: xl69"]58[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]12[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]70[/TD]
[TD="class: xl69"]71[/TD]
[TD="class: xl69"]72[/TD]
[TD="class: xl70"]103[/TD]
[TD="class: xl70"]104[/TD]
[TD="class: xl70"]169[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]71[/TD]
[TD="class: xl69"]72[/TD]
[TD="class: xl70"]104[/TD]
[TD="class: xl70"]169[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]72[/TD]
[TD="class: xl70"]169[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]82[/TD]
[TD="class: xl69"]83[/TD]
[TD="class: xl69"]84[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]83[/TD]
[TD="class: xl69"]84[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]86[/TD]
[TD="class: xl69"]87[/TD]
[TD="class: xl69"]88[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]87[/TD]
[TD="class: xl69"]88[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]99[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]103[/TD]
[TD="class: xl69"]104[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]106[/TD]
[TD="class: xl69"]107[/TD]
[TD="class: xl69"]108[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl70"]189[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]107[/TD]
[TD="class: xl69"]108[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]110[/TD]
[TD="class: xl69"]111[/TD]
[TD="class: xl69"]112[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]111[/TD]
[TD="class: xl69"]112[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]114[/TD]
[TD="class: xl69"]115[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]134[/TD]
[TD="class: xl69"]135[/TD]
[TD="class: xl69"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]135[/TD]
[TD="class: xl69"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]157[/TD]
[TD="class: xl69"]158[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]158[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]166[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl69"]167[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[jarjarbingie]

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)
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65"]Member[/TD]
[TD="class: xl65, width: 64"]HCC[/TD]
[TD="width: 64"]Output Should look like[/TD]
[TD="width: 64"][/TD]
[TD="class: xl67, width: 64"]Highest Ranked HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[TD="class: xl68, width: 64"]Low HCC[/TD]
[/TR]
[TR]
[TD="class: xl71"]1[/TD]
[TD="class: xl71"]47[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl69"]9[/TD]
[TD="class: xl69"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]18[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl69"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]18[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl69"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]106[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl69"]12[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]114[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl69"]18[/TD]
[TD="class: xl69"]19[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl71"]161[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl69"]19[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl71"]111[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl69"]28[/TD]
[TD="class: xl69"]29[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl71"]112[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl69"]29[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]8[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="class: xl66"]46[/TD]
[TD="class: xl70"]48[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]54[/TD]
[TD="class: xl69"]55[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]11[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]57[/TD]
[TD="class: xl69"]58[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl71"]12[/TD]
[TD]0[/TD]
[TD][/TD]
[TD="class: xl66"]70[/TD]
[TD="class: xl69"]71[/TD]
[TD="class: xl69"]72[/TD]
[TD="class: xl70"]103[/TD]
[TD="class: xl70"]104[/TD]
[TD="class: xl70"]169[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]71[/TD]
[TD="class: xl69"]72[/TD]
[TD="class: xl70"]104[/TD]
[TD="class: xl70"]169[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]72[/TD]
[TD="class: xl70"]169[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]82[/TD]
[TD="class: xl69"]83[/TD]
[TD="class: xl69"]84[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]83[/TD]
[TD="class: xl69"]84[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]86[/TD]
[TD="class: xl69"]87[/TD]
[TD="class: xl69"]88[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]87[/TD]
[TD="class: xl69"]88[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]99[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]103[/TD]
[TD="class: xl69"]104[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]106[/TD]
[TD="class: xl69"]107[/TD]
[TD="class: xl69"]108[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl70"]189[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]107[/TD]
[TD="class: xl69"]108[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]110[/TD]
[TD="class: xl69"]111[/TD]
[TD="class: xl69"]112[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]111[/TD]
[TD="class: xl69"]112[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]114[/TD]
[TD="class: xl69"]115[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]134[/TD]
[TD="class: xl69"]135[/TD]
[TD="class: xl69"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]135[/TD]
[TD="class: xl69"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]136[/TD]
[TD="class: xl69"]137[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]157[/TD]
[TD="class: xl69"]158[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]158[/TD]
[TD="class: xl70"]161[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]166[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl69"]167[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

try this:

assuming table 1 is in columns A:B and table 2 is in columns G:L and that HCCs are sorted in ascending order per member in table 1:

C2 = MAX(0,MIN(IF($G$2:$L$32=$B2,ROW($G$2:$L$32),""))-1) press CTRL+SHIFT+ENTER
D2 = IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)=1,1,0)

 

[ask2tsp]

I tried the following, getting it half right

Excel Workbook
	A	B	C	D	E	F	G
1	Member	HCC		unique?	trumped?	unique and not trumped	Output Should look like
2	1	47		WAAR	ONWAAR	1	1
3	2	18		WAAR	WAAR	0	1
4	2	18		ONWAAR	WAAR	0	0
5	2	106		ONWAAR	ONWAAR	0	1
6	2	114		ONWAAR	ONWAAR	0	1
7	2	161		ONWAAR	WAAR	0	0
8	3	111		WAAR	WAAR	0	1
9	3	112		ONWAAR	WAAR	0	0
10	4	8		WAAR	ONWAAR	1	1
11	4	8		ONWAAR	ONWAAR	0	0
12	4	11		ONWAAR	WAAR	0	0
13	4	12		ONWAAR	WAAR	0	0
Sheet

Excel Workbook
	A	B	C	D	E	F
1		Low HCC	Low HCC	Low HCC	Low HCC	Low HCC
2	Highest Ranked HCC	1	2	3	4	5
3	8	9	10	11	12
4	9	10	11	12
5	10	11	12
6	11	12
7	17	18	19
8	18	19
9	27	28	29	80
10	28	29
11	46	48
12	54	55
13	57	58
14	70	71	72	103	104	169
15	71	72	104	169
16	72	169
17	82	83	84
18	83	84
19	86	87	88
20	87	88
21	99	100
22	103	104
23	106	107	108	161	189
24	107	108
25	110	111	112
26	111	112
27	114	115
28	134	135	136	137
29	135	136	137
30	136	137
31	157	158	161
32	158	161
33	166	80	167
Sheet

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 don't understand the for the same member part. please explain.

 

[ctowner]

I don't understand the for the same member part. please explain.

So if a member has 3 HCCs being (18, 18, 19). The output should have an output of (1, 0, 0) for the 3 HCCs because the formula should only output a 1the first UNIQUE HCC for the member. However, going by the table, HCC 18 outweighs HCC 19, therefore HCC 19 should output a 0 since it's the same member. Does that help? Thanks.

 

[ctowner]

Hate to bump my old topic but it's nearly a year later and still have no full proof solution to this