Leveraging TEXTJOIN

[CCSlice]

Hi Everyone,

I am looking to leverage the TEXTJOIN function in Office 365 version to return which files are currently opened in a list. The function itself returns the names of the people who have currently checked out files (Column F), but what I would like returned is the name of the files (Column C) that are currently opened.

Sample File.xlsx A B C D E F 1 Location Date File Link Modified Date File Currently Checked Out To 2 T:\Data Sets\ 2022-03-08 eLog_Daniel.xlsx Link 2022-02-23 3 T:\Data Sets\ 2022-03-08 eLog_Joseph.xlsx Link 2022-02-22 Joseph Sommers 4 T:\Data Sets\ 2022-03-08 eLog_Miriam.xlsx Link 2022-02-21 5 T:\Data Sets\ 2022-03-08 eLog_Deborah.xlsx Link 2022-02-23 Deborah Teagan 6 T:\Data Sets\ 2022-03-08 eLog_Karen.xlsx Link 2022-02-24 7 T:\Data Sets\ 2022-03-08 eLog_John.xlsx Link 2022-02-26 John Dailey 8 T:\Data Sets\ 2022-03-08 eLog_Philip.xlsx Link 2022-02-27 Philip Mountcastle 9 T:\Data Sets\ 2022-03-08 eLog_Isaac.xlsx Link 2022-02-17 SharePoint List Cell Formulas Range Formula D2:D9 D2 =HYPERLINK(A1&C1,"Link") My thanks in advance for all the help with this challenge.

 

[JamesCanale]

Do you want a comma-separated list of filenames where column F has a value in it?

=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))

 

[CCSlice]

Do you want a comma-separated list of filenames where column F has a value in it?

=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))

Yes. This works. However, I am limited in the real estate in which I can place the opened file names. I tried using an IFS Statement to write a message based upon the number of files opened but it is not working.

 

[CCSlice]

I am trying to write a conditional statement, to make this easier:

Sample File.xlsx
	A
1	=IFS(COUNTIF(SPL_DATA[File Currently Checked Out To],"<>")=0,"",COUNTIF(SPL_DATA[File Currently Checked Out To],"<>")<5,"Warning: These files are open: "& TEXTJOIN(" | ",TRUE,IF(SPL_DATA[File Currently Check Out To]<>"",SPL_DATA[File],"<>")),TRUE,"Warning: " & COUNTIF(SPL_DATA[File Currently Checked Out To],"<>") & " of the activity files are checked out")
Alerts

The Formula used above is =IFS(COUNTIF(SPL_DATA[File Currently Checked Out To],"<>")=0,"",COUNTIF(SPL_DATA[File Currently Checked Out To],"<>")<5,"Warning: These files are open: "& TEXTJOIN(" | ",TRUE,IF(SPL_DATA[File Currently Check Out To]<>"",SPL_DATA[File],"<>")),TRUE,"Warning: " & COUNTIF(SPL_DATA[File Currently Checked Out To],"<>") & " of the activity files are checked out")

Sample File.xlsx
	A	B	C	D	E	F
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22	Joseph Sommers
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23	Deborah Teagan
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26	John Dailey
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27	Philip Mountcastle
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17
SharePoint List

Cell Formulas
Range	Formula
D2:D9	D2	=HYPERLINK(A1&C1,"Link")

 

[JamesCanale]

What output are you looking for on this example?

 

[CCSlice]

Hi James,
Thanks for helping. The TEXTJOIN function is great but I am thinking as this list expands, the list will be too large to display in a cell of a spreadsheet.
Alternatively, I would like to show the following messages:

1. If there are no files currently checked out, show absolutely nothing or "No files are checked out" OR
2. If there are no more than 4 files checked out, show the names of those files opened OR
3. If neither of the above conditions are met, show me the number of files checked out.

That's it.

Sample results would be:
1. Blank or "No files are checked out"
2. Warning: These files are open: eLog_Joseph.xlsx | eLog_Deborah.xlsx | eLog_John.xlsx | eLog_Philip.xlsx
3. Warning: 7 activity files are currently checked out.

 

[JamesCanale]

I didn't use textjoin here, but you could replace what I have in the second conditional with the formula in A11.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	3/8/2022	eLog_Daniel.xlsx	Link	2/23/2022
3	T:\Data Sets\	3/8/2022	eLog_Joseph.xlsx	Link	2/22/2022	Joseph Sommers
4	T:\Data Sets\	3/8/2022	eLog_Miriam.xlsx	Link	2/21/2022
5	T:\Data Sets\	3/8/2022	eLog_Deborah.xlsx	Link	2/23/2022	Deborah Teagan
6	T:\Data Sets\	3/8/2022	eLog_Karen.xlsx	Link	2/24/2022
7	T:\Data Sets\	3/8/2022	eLog_John.xlsx	Link	2/26/2022	John Dailey
8	T:\Data Sets\	3/8/2022	eLog_Philip.xlsx	Link	2/27/2022	Philip Mountcastle
9	T:\Data Sets\	3/8/2022	eLog_Isaac.xlsx	Link	2/17/2022
10
11	eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
12
13	eLog_Joseph.xlsx
14	eLog_Deborah.xlsx
15	eLog_John.xlsx
16	eLog_Philip.xlsx
Sheet15

Cell Formulas
Range	Formula
D2:D9	D2	=HYPERLINK(A1&C1,"Link")
A11	A11	=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
A13:A16	A13	=IFS(COUNTA(F2:F9)=0,"No files checked out",COUNTA(F2:F9)<5,FILTER(C2:C9,F2:F9<>"",""),TRUE,COUNTA(F2:F9)&" files checked out")
Dynamic array formulas.

This looks like what you made. The detail somewhere must have got your wrong - or I don't have it right.

 

[CCSlice]

I didn't use textjoin here, but you could replace what I have in the second conditional with the formula in A11.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	3/8/2022	eLog_Daniel.xlsx	Link	2/23/2022
3	T:\Data Sets\	3/8/2022	eLog_Joseph.xlsx	Link	2/22/2022	Joseph Sommers
4	T:\Data Sets\	3/8/2022	eLog_Miriam.xlsx	Link	2/21/2022
5	T:\Data Sets\	3/8/2022	eLog_Deborah.xlsx	Link	2/23/2022	Deborah Teagan
6	T:\Data Sets\	3/8/2022	eLog_Karen.xlsx	Link	2/24/2022
7	T:\Data Sets\	3/8/2022	eLog_John.xlsx	Link	2/26/2022	John Dailey
8	T:\Data Sets\	3/8/2022	eLog_Philip.xlsx	Link	2/27/2022	Philip Mountcastle
9	T:\Data Sets\	3/8/2022	eLog_Isaac.xlsx	Link	2/17/2022
10
11	eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
12
13	eLog_Joseph.xlsx
14	eLog_Deborah.xlsx
15	eLog_John.xlsx
16	eLog_Philip.xlsx
Sheet15

Cell Formulas
Range	Formula
D2:D9	D2	=HYPERLINK(A1&C1,"Link")
A11	A11	=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
A13:A16	A13	=IFS(COUNTA(F2:F9)=0,"No files checked out",COUNTA(F2:F9)<5,FILTER(C2:C9,F2:F9<>"",""),TRUE,COUNTA(F2:F9)&" files checked out")
Dynamic array formulas.

This looks like what you made. The detail somewhere must have got your wrong - or I don't have it right.

This is good. But not exactly what I need. You have the results showing vertically, but I need to show it horizontally as one continuous statement. And I am not sure if capturing the condition of 5 or more files populated. It seems that your formula misses that.

 

[Peter_SSs]

Assuming that you have access to MS365 functions, including LET, see if this is what you want.
I have provided examples of all three circumstances.

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23			No files are checked out
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23			Warning: These files are open: eLog_Joseph.xlsx | eLog_Deborah.xlsx | eLog_John.xlsx | eLog_Philip.xlsx
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22	Joseph Sommers
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23	Deborah Teagan
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26	John Dailey
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27	Philip Mountcastle
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23	Tom		Warning: 6 activity files are currently checked out
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22	Joseph Sommers
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23	Deborah Teagan
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26	John Dailey
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27	Philip Mountcastle
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17	Dan
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

 

[JamesCanale]

This is good. But not exactly what I need. You have the results showing vertically, but I need to show it horizontally as one continuous statement. And I am not sure if capturing the condition of 5 or more files populated. It seems that your formula misses that.

This shows the output like my original solution (with a text join). It also had worked as I think you want it for 5 files checked out.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	3/8/2022	eLog_Daniel.xlsx	Link	2/23/2022
3	T:\Data Sets\	3/8/2022	eLog_Joseph.xlsx	Link	2/22/2022	Joseph Sommers
4	T:\Data Sets\	3/8/2022	eLog_Miriam.xlsx	Link	2/21/2022
5	T:\Data Sets\	3/8/2022	eLog_Deborah.xlsx	Link	2/23/2022	Deborah Teagan
6	T:\Data Sets\	3/8/2022	eLog_Karen.xlsx	Link	2/24/2022
7	T:\Data Sets\	3/8/2022	eLog_John.xlsx	Link	2/26/2022	John Dailey
8	T:\Data Sets\	3/8/2022	eLog_Philip.xlsx	Link	2/27/2022	Philip Mountcastle
9	T:\Data Sets\	3/8/2022	eLog_Isaac.xlsx	Link	2/17/2022
10
11	eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
Sheet15

Cell Formulas
Range	Formula
D2:D9	D2	=HYPERLINK(A1&C1,"Link")
A11	A11	=IFS(COUNTA(F2:F9)=0,"No files checked out",COUNTA(F2:F9)<5,TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,"")),TRUE,COUNTA(F2:F9)&" files checked out")