Forum Discussion

SparrowTail's avatar
SparrowTail
Copper Contributor
Sep 14, 2023
Solved

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's avatar
    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's avatar
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's avatar
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!