Syntax problems - What syntax to use with XLOOKUP, FILTER and TEXTJOIN nested functions...

[Voodoowho]

I have 2 worksheets in the same workbook, STAFF and Objectives.
Each tab contains a table...
'STAFF' holds tblStaff and 'Objectives' holds tblObjectives.

The tblOjectives has several headers with each staff member's names (col G:R), under which are cells containing the date that a certain objective name (col B) was added, or blank if not.

On the STAFF tab, tblStaff holds records of the same staff member names (col A) and a column (J) labeled 'Objectives' where I want to return the objective names, separated by a comma, from the tblObjectives for each staff name.

I'm trying to use XLOOKUP, FILTER and TEXTJOINE functions, but I can't get it to work. Is it even possible to do this?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Voodoowho]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The problem is that I work for a governmental agency and the restrictions are heavy for downloading and installing add-ins. I can't even use Macros that are not already approved and pre-installed. What would be the best way to show what I'm trying to do given the restrictions?

 

[Voodoowho]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The problem is that I work for a governmental agency and the restrictions are heavy for downloading and installing add-ins. I can't even use Macros that are not already approved and pre-installed. What would be the best way to show what I'm trying to do given the restrictions?

Never mind. I was able to install the add-in with no issues. I'll see what I can do with it. Thanks!

 

[Voodoowho]

Staff Work Reviewed.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Staff Name	Title	Creditable Serivice Date	Seniority Date	Continuous Service Date	Time in State Service	Date Hired in Title	Time in Current Position	Next Evaluation Due	Objectives
2	Tracie	ESPR	9/1/2022	4/18/2016	4/18/2016	6 Years 6 Months 16 Days	3/1/2018	4 Years 8 Months 2 Days	1/1/2023
3	Julie	ESPR	9/1/2022	6/1/2015	6/1/2015	7 Years 5 Months 2 Days	9/1/2020	2 Years 2 Months 2 Days	9/1/2023
4	Josh	ESPR	10/1/2021	10/16/2017	10/16/2017	5 Years 0 Months 18 Days	10/1/2017	5 Years 1 Months 2 Days	10/1/2023
5	Angela	ESPR	5/1/2022	5/8/2017	5/4/2015	5 Years 5 Months 26 Days	2/16/2021	1 Years 8 Months 18 Days	2/1/2023
STAFF

Cell Formulas
Range	Formula
F2:F5	F2	=DATEDIF([@[Seniority Date]],TODAY(),"y")&" Years "&DATEDIF([@[Seniority Date]],TODAY(),"ym")&" Months "&DATEDIF([@[Seniority Date]],TODAY(),"md")&" Days"
H2:H5	H2	=DATEDIF([@[Date Hired in Title]],TODAY(),"y")&" Years "&DATEDIF([@[Date Hired in Title]],TODAY(),"ym")&" Months "&DATEDIF([@[Date Hired in Title]],TODAY(),"md")&" Days"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I14	Dates Occurring	next month	text	NO

There are several more names on the above table.

And on a different worksheet tab in the same workbook...

Staff Work Reviewed.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Number	Category	Met	Exceeded	Methodology	Language	Angela	Cheryl	David	Mitchell	JoAnna	Julie	Josh	Tracie	Whitney	Michelle	Brian	Marcus	Notes:
2	1	Adjudication BTQ	75% = 95+ Score	76% - 100% = 95+ Score	Review	of non-separation cases have a BTQ score of 95 or greater.	2/1/2022	9/1/2022	10/1/2022	9/1/2022	10/1/2021				1/1/2022	1/1/2022	1/1/2022	1/1/2222
3	2	Adjudication Timeliness - Scheduled issues	80%	81% - 100%	ADJ007 / ADJ003	of determinations completed within 21 days of the Issue Detection Date.				9/1/2022
4	3	Assignment Accuracy	90% - 95% = Correct	96% - 100% Correct	Review	of assignments completed are correct.			10/1/2022
5	4	Claim Entry - Accuracy	92% - 95% of sample = 100% correct	96% - 100% of sample = 100% correct	Review	Reviews should consider: wage information (mondet vs. Pseudo), separation details (employment infor for all employers on mondet), correct LEU, correct BCE and correct issues added.		9/1/2022
6	5	Claims - (Entry Level 2)	???	???	Review	To gain understanding of basic claims processing including, but not limited to claim types, program types, affidavits, dependency allowance, benefit charging and issue detection. Attend and successfully complete basic claims training.
7	6	Claims - (Entry Level)	???	???	Review	Learn and understand how to navigate in the Illinois Benefits Information System (IBIS), Illinois Job Link (IJL) and Gentax.
8	7	Communication	No valid complaints	???	???	Effectively comunicates with colleagues, supervisor, partners and customers in a respectful manner.							10/1/2022			1/1/2022
9	8	Data Entry	???	???	???	All data entry is completed accurately and timely. Including, but not limited to, updating demographics, entering services, IJL Self Tracker, SharePoint entries, weekly/monthly reports, etc.								3/1/2022
10	9	Document Processing	90% - 95% = Correct	96% - 100% Correct	Review	of documents processed and reviewd are correctly assigned to the correct claimant, indexed and attached to the issue, and the appropriate work-flow assigned.							10/1/2022
11	10	Employment Service - (Entry Level)	???	???	Observation	Understand proper documentation within IJL for all services provided to Job Seekers to indicate that appropriate activities were performed to warrant credit for same services.						9/1/2022							To be revised for Julie on 12/1/22.
12	11	Employment Services - Workshops	???	???	???	Virtual Workshops: Facilitate workshops for clients. This includes the preparation and folow-up work for workshops hosted or co-hosted by IDES Effingham office. Make sure all relevant information is submitted correctly and in a timely manner to Sharepoint.							10/1/2022
13	12	ES Programs	???	???	???	Understand IDES programs related to Employment Services, including, but not limited to, Work Opportunity Tax Credit (WOTC), Fidelity Bonding and Re-Employment Services (RES) and Re-Employment Services and Eligibility Assessment (RESEA).						9/1/2022	10/1/2022	3/1/2022
14	13	ES Workshops - (Entry Level)	Attend/Participate in 1	Attend/Participate in 2 or more per month (24+)	Webex Records	Work with partners and ES Staff to help organize and/or facilitate one job seeker workshop per month.													To be revised for Julie on 12/1/22.
Objectives

There are more Objectives as records on this table as well that are not included.

I want to return a comma separated list of Objectives (listed under Category in tblObjectives) into the 'Objectives' column in tblStaff where there is a date under the same named columns in tblObjectives.

 

[Fluff]

Thanks for that, how about

=LET(f,FILTER(tblObjectives,(tblObjectives[#Headers]=[@[Staff Name]])+(tblObjectives[#Headers]=tblObjectives[[#Headers],[Category]])),TEXTJOIN(", ",,FILTER(INDEX(f,,1),INDEX(f,,2)<>"")))

 

[Voodoowho]

Thanks for that, how about

=LET(f,FILTER(tblObjectives,(tblObjectives[#Headers]=[@[Staff Name]])+(tblObjectives[#Headers]=tblObjectives[[#Headers],[Category]])),TEXTJOIN(", ",,FILTER(INDEX(f,,1),INDEX(f,,2)<>"")))

This seems to work, but only for the first 4 records in the table. The rest, after the first 4 are giving #REF errors. It may be because not every 'Category' has a date for at least one rep. If there isn't a way around it, I can just put unused 'Category' items onto another table or delete them. But it would be best to leave them on there and just somehow handle the blanks.

 

[Fluff]

Works for me no matter how many rows you have

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Staff Name	Title	Creditable Serivice Date	Seniority Date	Continuous Service Date	Time in State Service	Date Hired in Title	Time in Current Position	Next Evaluation Due	Objectives
2	Tracie	ESPR	01/09/2022	18/04/2016	18/04/2016	6 Years 6 Months 17 Days	01/03/2018	4 Years 8 Months 3 Days	01/01/2023	Data Entry, ES Programs
3	Julie	ESPR	01/09/2022	01/06/2015	01/06/2015	7 Years 5 Months 3 Days	01/09/2020	2 Years 2 Months 3 Days	01/09/2023	Employment Service - (Entry Level), ES Programs
4	Josh	ESPR	01/10/2021	16/10/2017	16/10/2017	5 Years 0 Months 19 Days	01/10/2017	5 Years 1 Months 3 Days	01/10/2023	Communication, Document Processing, Employment Services - Workshops, ES Programs
5	Angela	ESPR	01/05/2022	08/05/2017	04/05/2015	5 Years 5 Months 27 Days	16/02/2021	1 Years 8 Months 19 Days	01/02/2023	Adjudication BTQ
6	marcus					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
7	Cheryl					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ, Claim Entry - Accuracy
8	David					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ, Assignment Accuracy
9	Whitney					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
10	Brian					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
Sheet2

Cell Formulas
Range	Formula
F2:F10	F2	=DATEDIF([@[Seniority Date]],TODAY(),"y")&" Years "&DATEDIF([@[Seniority Date]],TODAY(),"ym")&" Months "&DATEDIF([@[Seniority Date]],TODAY(),"md")&" Days"
H2:H10	H2	=DATEDIF([@[Date Hired in Title]],TODAY(),"y")&" Years "&DATEDIF([@[Date Hired in Title]],TODAY(),"ym")&" Months "&DATEDIF([@[Date Hired in Title]],TODAY(),"md")&" Days"
J2:J10	J2	=LET(f,FILTER(tblObjectives,(tblObjectives[#Headers]=[@[Staff Name]])+(tblObjectives[#Headers]=tblObjectives[[#Headers],[Category]])),TEXTJOIN(", ",,FILTER(INDEX(f,,1),INDEX(f,,2)<>"")))

 

[Voodoowho]

Works for me no matter how many rows you have

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Staff Name	Title	Creditable Serivice Date	Seniority Date	Continuous Service Date	Time in State Service	Date Hired in Title	Time in Current Position	Next Evaluation Due	Objectives
2	Tracie	ESPR	01/09/2022	18/04/2016	18/04/2016	6 Years 6 Months 17 Days	01/03/2018	4 Years 8 Months 3 Days	01/01/2023	Data Entry, ES Programs
3	Julie	ESPR	01/09/2022	01/06/2015	01/06/2015	7 Years 5 Months 3 Days	01/09/2020	2 Years 2 Months 3 Days	01/09/2023	Employment Service - (Entry Level), ES Programs
4	Josh	ESPR	01/10/2021	16/10/2017	16/10/2017	5 Years 0 Months 19 Days	01/10/2017	5 Years 1 Months 3 Days	01/10/2023	Communication, Document Processing, Employment Services - Workshops, ES Programs
5	Angela	ESPR	01/05/2022	08/05/2017	04/05/2015	5 Years 5 Months 27 Days	16/02/2021	1 Years 8 Months 19 Days	01/02/2023	Adjudication BTQ
6	marcus					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
7	Cheryl					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ, Claim Entry - Accuracy
8	David					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ, Assignment Accuracy
9	Whitney					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
10	Brian					122 Years 10 Months 4 Days		122 Years 10 Months 4 Days		Adjudication BTQ
Sheet2

Cell Formulas
Range	Formula
F2:F10	F2	=DATEDIF([@[Seniority Date]],TODAY(),"y")&" Years "&DATEDIF([@[Seniority Date]],TODAY(),"ym")&" Months "&DATEDIF([@[Seniority Date]],TODAY(),"md")&" Days"
H2:H10	H2	=DATEDIF([@[Date Hired in Title]],TODAY(),"y")&" Years "&DATEDIF([@[Date Hired in Title]],TODAY(),"ym")&" Months "&DATEDIF([@[Date Hired in Title]],TODAY(),"md")&" Days"
J2:J10	J2	=LET(f,FILTER(tblObjectives,(tblObjectives[#Headers]=[@[Staff Name]])+(tblObjectives[#Headers]=tblObjectives[[#Headers],[Category]])),TEXTJOIN(", ",,FILTER(INDEX(f,,1),INDEX(f,,2)<>"")))

I figured it out. I had removed the last names for the records to protect the innocent. Lol! Thank you so much! Beautiful solution!!!
How do I go back and change the names back to first and last name? If I just add the last names back to the tblObjectives and put "" around the outside of the [ ]s on the names, will that fix it?

Again, never mind. I'm pretty thick on Friday. Lol. I think I have it now. Just needed to fix the names and then apply the formula. I'll let you know how it goes. Thanks again!

 

[Fluff]

Do both Staff names & the table headers include the full name?