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

[SparrowTail]

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 a list of users with their current roles to IT for verification.

This will make more sense in the screenshots so feel free to skip to those but here's a wall of text anyway. The way this raw data is exported is that there's a spreadsheet of user IDs along with the rights they have. On a separate sheet, we have a list of roles with their defined rights. I.E. one role can have multiple rights (example, a technician has the right to initiate a request as well as to create a work order - those are two separate rights under a single role). A user can also have multiple roles (e.g. one person can be a supervisor, and an admin at the same time).

IT wants a clean list with the roles of each user listed next to their names in CSV format. The problem is, there are nearly 20,000 entries in the exported data.

Here's a very rudimentary example of what I mean. Sheet 1 has the user list along with their rights:

Here's the other sheet with the roles defined based on user rights:

One suggestion I received was to use a function like

TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table2[Role],COUNTIFS(Table1[User],A2,Table1[Rights],Table2[Rights]))))

However, this makes it so that everyone populates with every role. In this case, even someone who's supposed to have view-only access is listed as an admin because I think the function is populating all the roles that have the ability to "create request".

[#2 · Accepted solution]

@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.

[#3]

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.

[#4]

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!