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)
MemberHCCOutput Should look likeHighest Ranked HCCLow HCCLow HCCLow HCCLow HCCLow HCC
147189101112
21819101112
2180101112
210611112
21141171819
216101819
3111127282980
311202829
4814648
4805455
41105758
4120707172103104169
7172104169
72169
828384
8384
868788
8788
99100
103104
106107108161189
107108
110111112
111112
114115
134135136137
135136137
136137
157158161
158161
16680167

<tbody>
</tbody>

Thank you.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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)
MemberHCCOutput Should look likeHighest Ranked HCCLow HCCLow HCCLow HCCLow HCCLow HCC
147189101112
21819101112
2180101112
210611112
21141171819
216101819
3111127282980
311202829
4814648
4805455
41105758
4120707172103104169
7172104169
72169
828384
8384
868788
8788
99100
103104
106107108161189
107108
110111112
111112
114115
134135136137
135136137
136137
157158161
158161
16680167

<tbody>
</tbody>

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,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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