Unique Textjoin with XLookup

gronfors

New Member
Joined
Aug 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!

Teams.xlsx
ABCDEFGHIJKLMN
1NamePositionTeamSupervisorHelperTeam1Team2Team3Team4LeaveTeamTeam with WildcardSupervisor
2Employee1EmployeeUnit A & Unit BSupervisor1 & Supervisor1NoUnit AUnit B  Unit A*Unit A*Supervisor1
3Employee2EmployeeUnit A & Unit DSupervisor1 & Supervisor3NoUnit AUnit D  Unit B*Unit B*Supervisor1
4Employee3EmployeeUnit DSupervisor3NoUnit D   Unit C*Unit C*Supervisor2
5Employee4EmployeeUnit E & Unit A & Unit BSupervisor5 & Supervisor1 & Supervisor1NoUnit EUnit AUnit B Unit D*Unit D*Supervisor3
6Employee5EmployeeUnit FN/ANoUnit F   Unit E*Unit E*Supervisor5
7Supervisor1SupervisorUnit A & Unit BN/ANoUnit AUnit B  
8Supervisor2SupervisorUnit CN/ANoUnit C   
9Supervisor3SupervisorUnit DN/ANoUnit D   
10Supervisor4SupervisorUnit EN/AOn LeaveUnit E   On leave
11Supervisor5SupervisorUnit EN/ANoUnit E   
Users
Cell Formulas
RangeFormula
D2:D11D2=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:E11E2=IF([@Leave]="On Leave", "On Leave", "No")
F2:F11F2=TRIM(IFERROR(LEFT([@Team],FIND("&",[@Team])-1),[@Team]))
G2:G11G2=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:H11H2=TRIM(MID(SUBSTITUTE([@Team],"&",REPT(" ",100)),200,100))
I2:I11I2=TRIM(MID(SUBSTITUTE([@Team], "&", REPT(" ", 999)), 2999, 999))
M2:M6M2="*"&L2&"*"
N2:N6N2=IFERROR(XLOOKUP(M2 & "Supervisor" & "No",Users[[#All],[Team]] & Users[[#All],[Position]] & Users[[#All],[Helper]],Users[[#All],[Name]],,2),"N/A")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D:DExpression=OR(D1="No Coaches Listed",D1="N/A")textNO
A:JExpression=$J1="On Leave"textNO
 
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1NamePositionTeamSupervisorHelperTeam1Team2Team3Team4LeaveTeamTeam with WildcardSupervisor
2Employee1EmployeeUnit A & Unit BSupervisor1NoUnit AUnit B  Unit A*Unit A*Supervisor1
3Employee2EmployeeUnit A & Unit DSupervisor1 & Supervisor3NoUnit AUnit D  Unit B*Unit B*Supervisor1
4Employee3EmployeeUnit DSupervisor3NoUnit D   Unit C*Unit C*Supervisor2
5Employee4EmployeeUnit E & Unit A & Unit BSupervisor5 & Supervisor1NoUnit EUnit AUnit B Unit D*Unit D*Supervisor3
6Employee5EmployeeUnit FN/ANoUnit F   Unit E*Unit E*Supervisor5
7Supervisor1SupervisorUnit A & Unit BN/ANoUnit AUnit B  
8Supervisor2SupervisorUnit CN/ANoUnit C   
9Supervisor3SupervisorUnit DN/ANoUnit D   
10Supervisor4SupervisorUnit EN/AOn LeaveUnit E   On leave
11Supervisor5SupervisorUnit EN/ANoUnit E   
Data
Cell Formulas
RangeFormula
D2:D11D2=IF(OR([@Position]="Manager",[@Position]="Supervisor"),"N/A",TEXTJOIN(" & ",,UNIQUE(XLOOKUP(TRIM(FILTERXML("<k><m>"&SUBSTITUTE([@Team]," & ","</m><m>")&"</m></k>","//m")),Teams[Team],Teams[Supervisor],"N/A",0))))
E2:E11E2=IF([@Leave]="On Leave", "On Leave", "No")
F2:F11F2=TRIM(IFERROR(LEFT([@Team],FIND("&",[@Team])-1),[@Team]))
G2:G11G2=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:H11H2=TRIM(MID(SUBSTITUTE([@Team],"&",REPT(" ",100)),200,100))
I2:I11I2=TRIM(MID(SUBSTITUTE([@Team], "&", REPT(" ", 999)), 2999, 999))
M2:M6M2="*"&L2&"*"
N2:N6N2=IFERROR(XLOOKUP(M2 & "Supervisor" & "No",Users[[#All],[Team]] & Users[[#All],[Position]] & Users[[#All],[Helper]],Users[[#All],[Name]],,2),"N/A")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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