Forum Discussion
Ella123
Jan 14, 2025Copper Contributor
Need help with my TEXTJOIN Formula
Hi all, I'm new to this forum and i have a question regarding this formula
=TEXTVERKETTEN(", "; WAHR; FILTER(
'Training Matrix'!$I$4:$AT$4; // training names
('Internal Tracking'!$A$4:$A$85 = $A5) * // Match employee name
ISTZAHL('Internal Tracking'!$E$4:$CX$85) * // Check for completion date
(INDEX('Training Matrix'!$I$5:$AT$92; VERGLEICH('Internal Tracking'!$E$3; 'Training Matrix'!$B$5:$B$92; 0); VERGLEICH(TEXT('Internal Tracking'!$B$4:$B$85; "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85; "0"); 'Training Matrix'!$I$4:$AT$4; 0)) = "M");
""
))
which is in english =TEXTJOIN(", ", TRUE, FILTER( 'Training Matrix'!$I$4:$AT$4, // training names ('Internal Tracking'!$A$4:$A$85 = $A5) // Match employee name
ISNUMBER('Internal Tracking'!$E$4:$CX$85) // Check for completion date
(INDEX('Training Matrix'!$I$5:$AT$92, MATCH('Internal Tracking'!$E$3, 'Training Matrix'!$B$5:$B$92, 0), MATCH(TEXT('Internal Tracking'!$B$4:$B$85, "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85, "0"), 'Training Matrix'!$I$4:$AT$4, 0)) = "M"), "" ))
I'm using a german excel so I'm working with the first one , regardless it gives an #WERT error which I haven't been able to fix, the components of the formula such as ISTZAHL(ISnumber) and Vergleich(Compare) works and I can't see where the mistake is .
To give a context on the formula and what I'm trying to achieve :
I have 3 sheets , the first one is the 'Training Matrix' .The Training Matrix is where you can see if a training according to role-section (in the header row) is mandatory (marked with a "M" ) or extra (marked with a "E").The trainings also differ according to section. As an example Engineer-SectionA has different mandatory/extra trainings than Engineer-SectionB, they are both engineers but because they are in different sections they have different trainings assigned as mandatory . In the screen shot the sections are blurred , but in the row 4 which begins from I column the format is as such "role-section".
Training Name= B5:B92 , Role-Section=I4:AY4 , M/E cells=I5:AY92
In the Internal Tracking sheet you can see if an employee has completed a training.If he/she completes it , there is a date in the cell , so the cells with no date indicate that the training is NOT completed and with conditional formating you can see if the completed training is mandatory or not but this information is not relevant for the formula.
Employee Name is in Column A4 , Employee Role in B4 , Section in D4, Training Names range : E3:E88 , date cells range : E4:CN85
What I'm trying to accomplish is in this sheet :
Using the formula , I want to retrive the name of the completed trainings for each employee.Employee Name is in A5:A86 , role in B5:B86 and section in D5:D86 in this sheet.
So for each person the completed mandatory trainings needs to be listed using the formula
=TEXTVERKETTEN(", "; WAHR; FILTER(
'Training Matrix'!$I$4:$AT$4; // training names
('Internal Tracking'!$A$4:$A$85 = $A5) * // Match employee name
ISTZAHL('Internal Tracking'!$E$4:$CX$85) * // Check for completion date
(INDEX('Training Matrix'!$I$5:$AT$92; VERGLEICH('Internal Tracking'!$E$3; 'Training Matrix'!$B$5:$B$92; 0); VERGLEICH(TEXT('Internal Tracking'!$B$4:$B$85; "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85; "0"); 'Training Matrix'!$I$4:$AT$4; 0)) = "M");
""
))
but it doesn't work , can someone help me out ? Thanks in advance !
The correct formula in F5 is
=TEXTJOIN("; ",TRUE,FILTER('Internal Tracking'!$E$3:$AB$3,ISNUMBER(FILTER('Internal Tracking'!$E$4:$AB$85, 'Internal Tracking'!$A$4:$A$85=$A5, "")), ""))
Auf Deutsch:
=TEXTVERKETTEN("; ";WAHR;FILTER('Internal Tracking'!$E$3:$AB$3;ISTZAHL(FILTER('Internal Tracking'!$E$4:$AB$85; 'Internal Tracking'!$A$4:$A$85=$A5; "")); ""))
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- ILY123Copper Contributor
https://docs.google.com/spreadsheets/d/1k-gIzs91YiNq_MAsPrD4QKyUiglbl11r/edit?usp=sharing&ouid=115058807617085367176&rtpof=true&sd=true here is the sample data, this is my other account as i couldn't share it with my work email, i hope it helps !
The correct formula in F5 is
=TEXTJOIN("; ",TRUE,FILTER('Internal Tracking'!$E$3:$AB$3,ISNUMBER(FILTER('Internal Tracking'!$E$4:$AB$85, 'Internal Tracking'!$A$4:$A$85=$A5, "")), ""))
Auf Deutsch:
=TEXTVERKETTEN("; ";WAHR;FILTER('Internal Tracking'!$E$3:$AB$3;ISTZAHL(FILTER('Internal Tracking'!$E$4:$AB$85; 'Internal Tracking'!$A$4:$A$85=$A5; "")); ""))