Help matching multiple permissions in columns

[Ashe]

The goal of this project is to create a table that shows whether or not associates have permissions to perform a task at work.

Column B contains associate logins.
Column C contains the task the associate is trained in.
Columns A and D contain irrelevant info that comes with the downloaded task file.

Associates can be trained in multiple tasks.
Example...

Etc etc...

This is what I'd ultimately like the end result to visually be.

The task file contains thousands of entries. Each task gets its own entry. If the associate doesn't have training, they don't appear on the list. I've tried [V/X/H]LOOKUP, INDEX, and MATCH in various combinations and haven't been able to get the document to return the correct information. It keeps getting stuck on the first entry it finds. Per the example, Cat has Stacking and it only looks at Cat having Stacking, completely ignoring that they don't have Sort, so it returns "Yes" for everything. Another problem is this list is dynamic. Alex could lose their Prep permissions tomorrow, and it would then be deleted from the list the next time it's updated.

I've previously achieved the results I want by having each permission as a column header with the associates trained within the respective column, but this new task file doesn't download like that. I could use VBA to adjust the document to a similar layout, but was hoping there may be a solution that doesn't require me setting that up.

Any help or suggestions are greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1						Sort	Prep	Stacking
2		Alex	Sort		Alex	Yes	Yes	Yes
3		Bob	Prep		Bob	No	Yes	No
4		Cat	Stacking		Cat	No	Yes	Yes
5		Alex	Stacking
6		Alex	Prep
7		Cat	Prep
8
Data

Cell Formulas
Range	Formula
F1:H1	F1	=TRANSPOSE(UNIQUE(C2:C7))
E2:E4	E2	=UNIQUE(B2:B7)
F2:H4	F2	=IF(COUNTIFS(B:B,E2#,C:C,F1#),"Yes","No")
Dynamic array formulas.

 

[Excel_Fann]

Hello
You could use the Pivot Table functionality for this.
Apply it on the source data and set up the resulting table on a new sheet.
You would then count the amounts of tasks that a person can do.
The Result would be:

	Sort	Prep	Stacking
Alex	1	1	1
Bob	0	1	0
Cat	0	1	1

It´s simple to setup, flexible and easy to update.

 

[Ashe]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1						Sort	Prep	Stacking
2		Alex	Sort		Alex	Yes	Yes	Yes
3		Bob	Prep		Bob	No	Yes	No
4		Cat	Stacking		Cat	No	Yes	Yes
5		Alex	Stacking
6		Alex	Prep
7		Cat	Prep
8
Data

Cell Formulas
Range	Formula
F1:H1	F1	=TRANSPOSE(UNIQUE(C2:C7))
E2:E4	E2	=UNIQUE(B2:B7)
F2:H4	F2	=IF(COUNTIFS(B:B,E2#,C:C,F1#),"Yes","No")
Dynamic array formulas.

Hello! Thank you for the welcome

Unfortunately that didn't work. I knew I'd miss some other info as my brain is a bit fried right now, so I apologize. Seeing that mini-sheet helps too. Maybe I can provide better context than just screenshots!

Here are the mini-sheets. I have a master file with all employee info on another sheet ('Master Roster'), and all of the permission data on another sheet ('Permissions'). Both sheets update when you open the file, which in turn updated the main page that hosts all the info. I have an old function in H4:J6 that was from the original document I had where it did what I wanted. I know it doesn't work here.

The document currently allows users to type in a login, and it produces the associate's name, employee ID, where they're at in the building depending on the day, if they're active in the system or on a leave, and if they're present in the building. My addition is the permissions. Hopefully it helps provide some more context!

Main page:

Learning Roster v0.1.xlsm
	A	B	C	D	E	F	G	H	I	J
3	Login	Associate	Employee ID	1st half	2nd half	Active?	Present?	Sort	Prep	Stacking
4	alex	Anderson, Alex	123	IB	IB	Active	N/A	Yes	No	No
5	bob	Baxter,Bob	456	IB	IB	Active	N/A	No	Yes	No
6	cat	Check,Cat	789	IB	IB	Active	N/A	No	No	Yes
7	0	#N/A	#N/A	IB	IB	#N/A	N/A
8	0	#N/A	#N/A	IB	IB	#N/A	N/A
Sheet1

Cell Formulas
Range	Formula
B4:B8	B4	=VLOOKUP(A4,'Master Roster'!B:C,2,0)
C4:C8	C4	=VLOOKUP(A4,'Master Roster'!B:E,4,0)
F4:F8	F4	=VLOOKUP(A4,'Master Roster'!B:E,3,0)
G4:G8	G4	=IF(IFERROR(VLOOKUP(C4,'TOT Pull'!A:A,1,0),"")="","N/A","Present")
H4:H7	H4	=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Sort","Yes","No"),"")
I4:I7	I4	=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Prep","Yes","No"),"")
J4:J7	J4	=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Stacking","Yes","No"),"")
H8	H8	=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="Carton PrEditor","Yes","No"),"")
I8	I8	=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="EPJ","Yes","No"),"")
J8	J8	=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="Each Receive","Yes","No"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:W13	Cell Value	contains "Yes"	text	NO
H4:W13	Cell Value	contains "No"	text	NO
A5:A13	Cell Value	not containing "0"	text	NO
A4	Cell Value	not containing "0"	text	NO

Master Roster:

Learning Roster v0.1.xlsm
	A	B	C	D	E
1	Employee ID	User ID	Employee Name	Employee Status	Employee ID
2	123	Alex	Anderson, Alex	Active	123
3	456	Bob	Baxter,Bob	Active	456
4	789	Cat	Check,Cat	Active	789
Master Roster

Permissions:

Learning Roster v0.1.xlsm
	A	B	C	D
1	SUPERVISOR_NAME	Associate Login	Process	earneddateutc
2	Manager 1	Alex	Sort	11/1/2023
3	Manager 2	Bob	Prep	11/2/2023
4	Manager 3	Cat	Stacking	11/3/2023
5	Manager 1	Alex	Stacking	11/4/2023
6	Manager 1	Alex	Prep	11/5/2023
7	Manager 3	Cat	Prep	11/6/2023
Permissions

 

[Fluff]

How about

=IF(COUNTIFS(Permissions!B:B,A4:A8,Permissions!C:C,H3:J3),"Yes","No")

 

[Ashe]

How about

=IF(COUNTIFS(Permissions!B:B,A4:A8,Permissions!C:C,H3:J3),"Yes","No")

YES, that did it. Oh my goodness. Thank you very much for your help!! You saved me a lot of headache

 

[Fluff]

You're welcome & thanks for the feedback.