I'm trying to utilize all the 365 functions today it seems!
End goal is to have an employee's supervisor listed based on their team and remove duplicates as some supervisors have multiple teams and some employees are considered on multiple teams.
Right now I have a separate table finding each supervisor an assigning it to a team. Then the primary table checks the employee's team, and lookups up the current supervisor in the table. I've then just messily duplicated that for up to 4 teams but I can't figure out how to work UNIQUE into this or MATCH.
Essentially I'd want Employee 1 to just have Supervisor1 listed instead of duplicated (and employee4 just have Supervisor5 & Supervisor1 listed)
Thanks for the help!
End goal is to have an employee's supervisor listed based on their team and remove duplicates as some supervisors have multiple teams and some employees are considered on multiple teams.
Right now I have a separate table finding each supervisor an assigning it to a team. Then the primary table checks the employee's team, and lookups up the current supervisor in the table. I've then just messily duplicated that for up to 4 teams but I can't figure out how to work UNIQUE into this or MATCH.
Essentially I'd want Employee 1 to just have Supervisor1 listed instead of duplicated (and employee4 just have Supervisor5 & Supervisor1 listed)
Thanks for the help!
Teams.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Name | Position | Team | Supervisor | Helper | Team1 | Team2 | Team3 | Team4 | Leave | Team | Team with Wildcard | Supervisor | |||
2 | Employee1 | Employee | Unit A & Unit B | Supervisor1 & Supervisor1 | No | Unit A | Unit B | Unit A | *Unit A* | Supervisor1 | ||||||
3 | Employee2 | Employee | Unit A & Unit D | Supervisor1 & Supervisor3 | No | Unit A | Unit D | Unit B | *Unit B* | Supervisor1 | ||||||
4 | Employee3 | Employee | Unit D | Supervisor3 | No | Unit D | Unit C | *Unit C* | Supervisor2 | |||||||
5 | Employee4 | Employee | Unit E & Unit A & Unit B | Supervisor5 & Supervisor1 & Supervisor1 | No | Unit E | Unit A | Unit B | Unit D | *Unit D* | Supervisor3 | |||||
6 | Employee5 | Employee | Unit F | N/A | No | Unit F | Unit E | *Unit E* | Supervisor5 | |||||||
7 | Supervisor1 | Supervisor | Unit A & Unit B | N/A | No | Unit A | Unit B | |||||||||
8 | Supervisor2 | Supervisor | Unit C | N/A | No | Unit C | ||||||||||
9 | Supervisor3 | Supervisor | Unit D | N/A | No | Unit D | ||||||||||
10 | Supervisor4 | Supervisor | Unit E | N/A | On Leave | Unit E | On leave | |||||||||
11 | Supervisor5 | Supervisor | Unit E | N/A | No | Unit E | ||||||||||
Users |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D11 | D2 | =IFERROR(IF(OR([@Position]="Manager",[@Position]="Supervisor"),"N/A",TEXTJOIN(" & ",TRUE,XLOOKUP([@Team1],L:L,N:N),IF([@Team2]="","",XLOOKUP([@Team2],L:L,N:N)),IF([@Team3]="","",XLOOKUP([@Team3],L:L,N:N)),IF([@Team4]="","",XLOOKUP([@Team4],L:L,N:N)))),"N/A") |
E2:E11 | E2 | =IF([@Leave]="On Leave", "On Leave", "No") |
F2:F11 | F2 | =TRIM(IFERROR(LEFT([@Team],FIND("&",[@Team])-1),[@Team])) |
G2:G11 | G2 | =TRIM(IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))<1,"",IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))=1,RIGHT([@Team],LEN([@Team]) - FIND("&",[@Team])),MID([@Team],SEARCH("&",[@Team]) + 1, SEARCH("&", [@Team],SEARCH("&",[@Team])+1) - SEARCH("&",[@Team])-1)))) |
H2:H11 | H2 | =TRIM(MID(SUBSTITUTE([@Team],"&",REPT(" ",100)),200,100)) |
I2:I11 | I2 | =TRIM(MID(SUBSTITUTE([@Team], "&", REPT(" ", 999)), 2999, 999)) |
M2:M6 | M2 | ="*"&L2&"*" |
N2:N6 | N2 | =IFERROR(XLOOKUP(M2 & "Supervisor" & "No",Users[[#All],[Team]] & Users[[#All],[Position]] & Users[[#All],[Helper]],Users[[#All],[Name]],,2),"N/A") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D:D | Expression | =OR(D1="No Coaches Listed",D1="N/A") | text | NO |
A:J | Expression | =$J1="On Leave" | text | NO |