Help matching multiple permissions in columns

Ashe

New Member
Joined
Dec 2, 2023
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
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...
1701544350860.png
Etc etc...

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


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!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi & welcome to MrExcel.
How about
Fluff.xlsm
ABCDEFGH
1SortPrepStacking
2AlexSortAlexYesYesYes
3BobPrepBobNoYesNo
4CatStackingCatNoYesYes
5AlexStacking
6AlexPrep
7CatPrep
8
Data
Cell Formulas
RangeFormula
F1:H1F1=TRANSPOSE(UNIQUE(C2:C7))
E2:E4E2=UNIQUE(B2:B7)
F2:H4F2=IF(COUNTIFS(B:B,E2#,C:C,F1#),"Yes","No")
Dynamic array formulas.
 
Upvote 0
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:
SortPrepStacking
Alex111
Bob010
Cat011

It´s simple to setup, flexible and easy to update.
 
Upvote 0
Hi & welcome to MrExcel.
How about
Fluff.xlsm
ABCDEFGH
1SortPrepStacking
2AlexSortAlexYesYesYes
3BobPrepBobNoYesNo
4CatStackingCatNoYesYes
5AlexStacking
6AlexPrep
7CatPrep
8
Data
Cell Formulas
RangeFormula
F1:H1F1=TRANSPOSE(UNIQUE(C2:C7))
E2:E4E2=UNIQUE(B2:B7)
F2:H4F2=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
ABCDEFGHIJ
3LoginAssociateEmployee ID1st half2nd halfActive?Present?SortPrepStacking
4alexAnderson, Alex123IBIBActiveN/AYesNoNo
5bobBaxter,Bob456IBIBActiveN/ANoYesNo
6catCheck,Cat789IBIBActiveN/ANoNoYes
70#N/A#N/AIBIB#N/AN/A   
80#N/A#N/AIBIB#N/AN/A   
Sheet1
Cell Formulas
RangeFormula
B4:B8B4=VLOOKUP(A4,'Master Roster'!B:C,2,0)
C4:C8C4=VLOOKUP(A4,'Master Roster'!B:E,4,0)
F4:F8F4=VLOOKUP(A4,'Master Roster'!B:E,3,0)
G4:G8G4=IF(IFERROR(VLOOKUP(C4,'TOT Pull'!A:A,1,0),"")="","N/A","Present")
H4:H7H4=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Sort","Yes","No"),"")
I4:I7I4=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Prep","Yes","No"),"")
J4:J7J4=IFNA(IF(VLOOKUP(A4,Permissions!$B:$C,2,FALSE)="Stacking","Yes","No"),"")
H8H8=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="Carton PrEditor","Yes","No"),"")
I8I8=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="EPJ","Yes","No"),"")
J8J8=IFNA(IF(VLOOKUP(A8,Permissions!$B:$C,2,FALSE)="Each Receive","Yes","No"),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H4:W13Cell Valuecontains "Yes"textNO
H4:W13Cell Valuecontains "No"textNO
A5:A13Cell Valuenot containing "0"textNO
A4Cell Valuenot containing "0"textNO

Master Roster:
Learning Roster v0.1.xlsm
ABCDE
1Employee IDUser IDEmployee NameEmployee StatusEmployee ID
2123AlexAnderson, AlexActive123
3456BobBaxter,BobActive456
4789CatCheck,CatActive789
Master Roster

Permissions:
Learning Roster v0.1.xlsm
ABCD
1SUPERVISOR_NAMEAssociate LoginProcessearneddateutc
2Manager 1AlexSort11/1/2023
3Manager 2BobPrep11/2/2023
4Manager 3CatStacking11/3/2023
5Manager 1AlexStacking11/4/2023
6Manager 1AlexPrep11/5/2023
7Manager 3CatPrep11/6/2023
Permissions
 
Upvote 0
How about
Excel Formula:
=IF(COUNTIFS(Permissions!B:B,A4:A8,Permissions!C:C,H3:J3),"Yes","No")
 
Upvote 0
Solution
How about
Excel Formula:
=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 💕
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top