Leveraging TEXTJOIN

CCSlice

CCSlice

New Member
Joined
Feb 11, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEF
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\2022-03-08eLog_Daniel.xlsxLink2022-02-23
3T:\Data Sets\2022-03-08eLog_Joseph.xlsxLink2022-02-22Joseph Sommers
4T:\Data Sets\2022-03-08eLog_Miriam.xlsxLink2022-02-21
5T:\Data Sets\2022-03-08eLog_Deborah.xlsxLink2022-02-23Deborah Teagan
6T:\Data Sets\2022-03-08eLog_Karen.xlsxLink2022-02-24
7T:\Data Sets\2022-03-08eLog_John.xlsxLink2022-02-26John Dailey
8T:\Data Sets\2022-03-08eLog_Philip.xlsxLink2022-02-27Philip Mountcastle
9T:\Data Sets\2022-03-08eLog_Isaac.xlsxLink2022-02-17
SharePoint List
Cell Formulas
RangeFormula
D2:D9D2=HYPERLINK(A1&C1,"Link")


My thanks in advance for all the help with this challenge.
 
Last edited by a moderator:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Do you want a comma-separated list of filenames where column F has a value in it?

Excel Formula: 
=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
 
Upvote 0
JamesCanale said:
Do you want a comma-separated list of filenames where column F has a value in it?

Excel Formula: 
=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
Click to expand...
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.
 
Upvote 0
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
ABCDEF
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\2022-03-08eLog_Daniel.xlsxLink2022-02-23
3T:\Data Sets\2022-03-08eLog_Joseph.xlsxLink2022-02-22Joseph Sommers
4T:\Data Sets\2022-03-08eLog_Miriam.xlsxLink2022-02-21
5T:\Data Sets\2022-03-08eLog_Deborah.xlsxLink2022-02-23Deborah Teagan
6T:\Data Sets\2022-03-08eLog_Karen.xlsxLink2022-02-24
7T:\Data Sets\2022-03-08eLog_John.xlsxLink2022-02-26John Dailey
8T:\Data Sets\2022-03-08eLog_Philip.xlsxLink2022-02-27Philip Mountcastle
9T:\Data Sets\2022-03-08eLog_Isaac.xlsxLink2022-02-17
SharePoint List
Cell Formulas
RangeFormula
D2:D9D2=HYPERLINK(A1&C1,"Link")
 
Upvote 0
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.
 
Upvote 0
I didn't use textjoin here, but you could replace what I have in the second conditional with the formula in A11.

MrExcelPlayground7.xlsx
ABCDEF
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\3/8/2022eLog_Daniel.xlsxLink2/23/2022
3T:\Data Sets\3/8/2022eLog_Joseph.xlsxLink2/22/2022Joseph Sommers
4T:\Data Sets\3/8/2022eLog_Miriam.xlsxLink2/21/2022
5T:\Data Sets\3/8/2022eLog_Deborah.xlsxLink2/23/2022Deborah Teagan
6T:\Data Sets\3/8/2022eLog_Karen.xlsxLink2/24/2022
7T:\Data Sets\3/8/2022eLog_John.xlsxLink2/26/2022John Dailey
8T:\Data Sets\3/8/2022eLog_Philip.xlsxLink2/27/2022Philip Mountcastle
9T:\Data Sets\3/8/2022eLog_Isaac.xlsxLink2/17/2022
10
11eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
12
13eLog_Joseph.xlsx
14eLog_Deborah.xlsx
15eLog_John.xlsx
16eLog_Philip.xlsx
Sheet15
Cell Formulas
RangeFormula
D2:D9D2=HYPERLINK(A1&C1,"Link")
A11A11=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
A13:A16A13=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.
 
Upvote 0
JamesCanale said:
I didn't use textjoin here, but you could replace what I have in the second conditional with the formula in A11.

MrExcelPlayground7.xlsx
ABCDEF
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\3/8/2022eLog_Daniel.xlsxLink2/23/2022
3T:\Data Sets\3/8/2022eLog_Joseph.xlsxLink2/22/2022Joseph Sommers
4T:\Data Sets\3/8/2022eLog_Miriam.xlsxLink2/21/2022
5T:\Data Sets\3/8/2022eLog_Deborah.xlsxLink2/23/2022Deborah Teagan
6T:\Data Sets\3/8/2022eLog_Karen.xlsxLink2/24/2022
7T:\Data Sets\3/8/2022eLog_John.xlsxLink2/26/2022John Dailey
8T:\Data Sets\3/8/2022eLog_Philip.xlsxLink2/27/2022Philip Mountcastle
9T:\Data Sets\3/8/2022eLog_Isaac.xlsxLink2/17/2022
10
11eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
12
13eLog_Joseph.xlsx
14eLog_Deborah.xlsx
15eLog_John.xlsx
16eLog_Philip.xlsx
Sheet15
Cell Formulas
RangeFormula
D2:D9D2=HYPERLINK(A1&C1,"Link")
A11A11=TEXTJOIN(", ",TRUE,IF(F2:F9<>"",C2:C9,""))
A13:A16A13=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.
Click to expand...

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.
 
Upvote 0
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
ABCDEFGH
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\2022-03-08eLog_Daniel.xlsxLink2022-02-23No files are checked out
3T:\Data Sets\2022-03-08eLog_Joseph.xlsxLink2022-02-22
4T:\Data Sets\2022-03-08eLog_Miriam.xlsxLink2022-02-21
5T:\Data Sets\2022-03-08eLog_Deborah.xlsxLink2022-02-23
6T:\Data Sets\2022-03-08eLog_Karen.xlsxLink2022-02-24
7T:\Data Sets\2022-03-08eLog_John.xlsxLink2022-02-26
8T:\Data Sets\2022-03-08eLog_Philip.xlsxLink2022-02-27
9T:\Data Sets\2022-03-08eLog_Isaac.xlsxLink2022-02-17
10
Sheet1
Cell Formulas
RangeFormula
H2H2=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
ABCDEFGH
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\2022-03-08eLog_Daniel.xlsxLink2022-02-23Warning: These files are open: eLog_Joseph.xlsx | eLog_Deborah.xlsx | eLog_John.xlsx | eLog_Philip.xlsx
3T:\Data Sets\2022-03-08eLog_Joseph.xlsxLink2022-02-22Joseph Sommers
4T:\Data Sets\2022-03-08eLog_Miriam.xlsxLink2022-02-21
5T:\Data Sets\2022-03-08eLog_Deborah.xlsxLink2022-02-23Deborah Teagan
6T:\Data Sets\2022-03-08eLog_Karen.xlsxLink2022-02-24
7T:\Data Sets\2022-03-08eLog_John.xlsxLink2022-02-26John Dailey
8T:\Data Sets\2022-03-08eLog_Philip.xlsxLink2022-02-27Philip Mountcastle
9T:\Data Sets\2022-03-08eLog_Isaac.xlsxLink2022-02-17
10
Sheet1
Cell Formulas
RangeFormula
H2H2=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
ABCDEFGH
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\2022-03-08eLog_Daniel.xlsxLink2022-02-23TomWarning: 6 activity files are currently checked out
3T:\Data Sets\2022-03-08eLog_Joseph.xlsxLink2022-02-22Joseph Sommers
4T:\Data Sets\2022-03-08eLog_Miriam.xlsxLink2022-02-21
5T:\Data Sets\2022-03-08eLog_Deborah.xlsxLink2022-02-23Deborah Teagan
6T:\Data Sets\2022-03-08eLog_Karen.xlsxLink2022-02-24
7T:\Data Sets\2022-03-08eLog_John.xlsxLink2022-02-26John Dailey
8T:\Data Sets\2022-03-08eLog_Philip.xlsxLink2022-02-27Philip Mountcastle
9T:\Data Sets\2022-03-08eLog_Isaac.xlsxLink2022-02-17Dan
10
Sheet1
Cell Formulas
RangeFormula
H2H2=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")))
 
Last edited:
Upvote 0
Solution
CCSlice said:
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.
Click to expand...
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
ABCDEF
1LocationDateFileLinkModified DateFile Currently Checked Out To
2T:\Data Sets\3/8/2022eLog_Daniel.xlsxLink2/23/2022
3T:\Data Sets\3/8/2022eLog_Joseph.xlsxLink2/22/2022Joseph Sommers
4T:\Data Sets\3/8/2022eLog_Miriam.xlsxLink2/21/2022
5T:\Data Sets\3/8/2022eLog_Deborah.xlsxLink2/23/2022Deborah Teagan
6T:\Data Sets\3/8/2022eLog_Karen.xlsxLink2/24/2022
7T:\Data Sets\3/8/2022eLog_John.xlsxLink2/26/2022John Dailey
8T:\Data Sets\3/8/2022eLog_Philip.xlsxLink2/27/2022Philip Mountcastle
9T:\Data Sets\3/8/2022eLog_Isaac.xlsxLink2/17/2022
10
11eLog_Joseph.xlsx, eLog_Deborah.xlsx, eLog_John.xlsx, eLog_Philip.xlsx
Sheet15
Cell Formulas
RangeFormula
D2:D9D2=HYPERLINK(A1&C1,"Link")
A11A11=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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Avoiding INDIRECT when referencing and comparing ranges in different workbooks
Replies
1
Views
803
RoryA
RoryA
B
  • Question Question
Logging First occurrence in calendar year
Replies
9
Views
368
KRice
K
F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
897
KRice
K
PS_Richard
  • Solved
Calculate Time Difference Per Day Based On Criteria
Replies
5
Views
766
Anthony47
A
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
241
Madam_Xavier
M

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

Share this page

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