Forum Discussion

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
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!
SparrowTail
Copper Contributor
Sep 14, 2023
Oh my gosh, wow I don't think I know half the formulae you used in there, are you some kind of a magician? Haha, thank you so much! I feel like I'm going to spend about a day translating this back to the original file that I have with those thousands of entries.