Excel Formula with Hierarchy Table

ctowner

New Member
Joined
Jul 24, 2017
Messages
3
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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)
 
Upvote 0
I tried the following, getting it half right
Excel Workbook
ABCDEFG
1MemberHCCunique?trumped?unique and not trumpedOutput Should look like
2147WAARONWAAR11
3218WAARWAAR01
4218ONWAARWAAR00
52106ONWAARONWAAR01
62114ONWAARONWAAR01
72161ONWAARWAAR00
83111WAARWAAR01
93112ONWAARWAAR00
1048WAARONWAAR11
1148ONWAARONWAAR00
12411ONWAARWAAR00
13412ONWAARWAAR00
Sheet
Excel Workbook
ABCDEF
1Low HCCLow HCCLow HCCLow HCCLow HCC
2Highest Ranked HCC12345
389101112
49101112
5101112
61112
7171819
81819
927282980
102829
114648
125455
135758
14707172103104169
157172104169
1672169
17828384
188384
19868788
208788
2199100
22103104
23106107108161189
24107108
25110111112
26111112
27114115
28134135136137
29135136137
30136137
31157158161
32158161
3316680167
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.
 
Upvote 0
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.
 
Upvote 0
Hate to bump my old topic but it's nearly a year later and still have no full proof solution to this :(
 
Upvote 0

Forum statistics

Threads
1,224,875
Messages
6,181,514
Members
453,050
Latest member
Obil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top