Help finding which software users belong in a certain role based on the rights they have

Hi all, I'm using Office 365 Excel (version 2302) So at work we have a software in which users can have multiple roles. Each role comes with its own list of rights. Every year, we have to provide...

excel

Formulas and Functions

OliverScheurich
Sep 14, 2023
SparrowTail
=DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(A2:A14))),LAMBDA(u,v,VSTACK(u,HSTACK(INDEX(UNIQUE(A2:A14),v),TEXTJOIN(",",,DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(E2:E12))),LAMBDA(x,y,VSTACK(x,IF(SUM(
IFNA(OR(XMATCH(FILTER(F2:F12,E2:E12=INDEX(UNIQUE(E2:E12),y)), FILTER(B2:B14,A2:A14=INDEX(UNIQUE(A2:A14),v)))),0))>0,INDEX(UNIQUE(E2:E12),y),"")))),1)))))),1)

I assume this can be simplified however it returns the intended result in my Excel for the web sheet.

OliverScheurich

Gold Contributor

Sep 14, 2023

SparrowTail

=DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(A2:A14))),LAMBDA(u,v,VSTACK(u,HSTACK(INDEX(UNIQUE(A2:A14),v),TEXTJOIN(",",,DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(E2:E12))),LAMBDA(x,y,VSTACK(x,IF(SUM(
IFNA(OR(XMATCH(FILTER(F2:F12,E2:E12=INDEX(UNIQUE(E2:E12),y)), FILTER(B2:B14,A2:A14=INDEX(UNIQUE(A2:A14),v)))),0))>0,INDEX(UNIQUE(E2:E12),y),"")))),1)))))),1)

I assume this can be simplified however it returns the intended result in my Excel for the web sheet.

SparrowTail

Copper Contributor

Sep 15, 2023

Confirming that this worked! My laptop almost burst into flames doing it for 19,000 rows and I thought Excel had crashed. Went to get coffee and came back to the laptop fan sounding like a jet engine but with the data populated!

Forum Discussion

Help finding which software users belong in a certain role based on the rights they have

Resources