Populating a matrix permissions hierarchy based on parent values

Greetings all,

I'm trying to populate implicit values in a hierarchy, but haven't had much luck.

This is my input sheet ("Input Matrix"):

01.png

I want to create an output sheet ("Output Matrix") that looks at the values in column D and fills in any missing:

02.png

Some context: this is a menu permissions hierarchy for a product that assumes all menu items are enabled unless explicitly disabled. The product is capable of storing a Y/N permission for every menu item at every level, but is often configured such that only a parent menu item is disabled; child items are implicitly disabled, but not explicitly. The objective is to pull table data for explicitly-disabled menu items and determine the full implicit security matrix.

The logic for column D in Output Matrix could be something like this:

Please Login or Register  to view this content.

This matrix will have a maximum of 7 layers, 50 users, and 1000 rows. Ideally the solution would not require VBA, unless this is required to keep performance at a reasonable level.

The first challenge to me is the "find parent" portion; I'm unsure how to "look up" from my starting cell to find the location of the first non-blank value in the adjacent column. Any suggestions much appreciated.

Hello alexdi and Welcome to Excel Forum.
This proposal employs a number of helper columns which may be moved and/or hidden for aesthetic purposes. The helper columns (E:I) are populated using relatively simple formulas so, hopefully, they will not produce a performance issue for a machine capable of running the 365 version of Excel.
Column E finds the layer and is populated using: =SUMPRODUCT((A2:C2<>"")*COLUMN(A2:C2))
Column F identifies the parent row and is populated using: =IF(E2=1,"",AGGREGATE(14,6,ROW(A$1:A1)/(E$1:E1<E2),1))
Column G returns permissions for layer 1 along with those explicitly stated and is populated using: =IF(D2<>"",D2,IF(E2=1,"Y",""))
Columns H:I (and presumably through M) returns the permissions of succeeding layers and is populated using: =IF(G2<>"",G2,INDEX(G$2:G$12,$F2-1))
Note that the values in column I match those in column D on the output matrix sheet.
Let us know if you have any questions.

Thank you, that solution is clever and functional for the example sheet. I used a similar mechanism elsewhere in a related sheet. Unfortunately I'm not able to adapt it here because scaling it to the full sheet becomes unwieldy. That sheet currently has 7 layers and 30 users. Because the helper columns except for Layer and Parent Row are specific to each user, I think I would need about 212 of them.

Do you know if there is a way to do this in one column, or with helper columns that are not user-specific?

I am not sure that I understand the difference that "users" make. Could you update the file attached to post #1 so that we can see how having two users would change the expected outcome?
Let us know if you have any questions.

See if the following would work:
1. On the Input Matrix sheet cell H4 has data validation applied so as to choose the user from the User Input sheet
2. Cells H5:H19 are populated using: =IF(INDEX('User Input'!A2:B2,MATCH(H$4,'User Input'!A$1:B$1,0))=0,"",INDEX('User Input'!A2:B2,MATCH(H$4,'User Input'!A$1:B$1,0)))
Let us know if you have any questions.

populating-a-matrix-permissions-hierarchy-based-on-parent-values-implicitpermissionstest_update.xlsx

Without helper columns, there is a bit going on. But try in Column K of the input tab use in Row 5

=IF(H5<>"",H5,IF(LOOKUP(2,1/($A5:$G5<>""),COLUMN($A5:$G5))=1,"Y",INDEX(K4:K$5,MATCH(LOOKUP(2,1/(INDEX($A$4:$G5,,LOOKUP(2,1/($A5:$G5<>""),COLUMN($A5:$G5))-1)<>""),INDEX($A$4:$G5,,LOOKUP(2,1/($A5:$G5<>""),COLUMN($A5:$G5))-1)),INDEX($A4:$G$5,,LOOKUP(2,1/($A5:$G5<>""),COLUMN($A5:$G5))-1),0))))

Copy down and across

populating-a-matrix-permissions-hierarchy-based-on-parent-values-implicitpermissionstest_update.xlsx

This works when the immediately preceding sub-layer is a unique value in that layer. If not, there needs to be an array formula which is a whole new level of complexity

In implementing this, I have an unrelated issue: the actual sheet is offset by a column (i.e., the relevant range to review is B:H, the check column is K, and the result column is AP). Fixing the check and result columns works, but when I try to modify the check range, the formula breaks. Excel reports circular references. I've been poking at it for an hour now and can't figure out why it's breaking. Any thoughts?

Assuming that when the offset is taken into account "User 1 Output" is in cell L4, Try pasting the following into cell L5 and then copy down and over:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.