TEXTJOIN alternative for older versions?

[adams2712]

Hi all, new poster but at my wits end with TEXTJOIN function. I created a spreadsheet for staffing and had it functioning at home in Office 365 using the TEXTJOIN function, however my systems at work use Office 2013 in most workstations, so my command stopped working. My command is as follows:
=TEXTJOIN(",",TRUE,IF(A6=B2:B27,A2:A23,""))

This provides a return of all staff names marked with either a "D" or an "N" in that day's column (I have placed a hidden "D" and "N" in cells A6 and A9 of the sheet to provide a reference value).
I was wondering if there was an easy way outside of VBA for this to be achieved using backwards compatible functions? See screenshots below for examples of the code working and the code broken in 2013.

 

[Fluff]

Hi & welcome to MrExcel.
You could use something like

=MID(IF(B2=A6,","&A2,"")&IF(B3=A6,","&A3,"")&IF(B4=A6,","&A4,""),2,1000)

 

[adams2712]

T

Hi & welcome to MrExcel.
You could use something like

=MID(IF(B2=A6,","&A2,"")&IF(B3=A6,","&A3,"")&IF(B4=A6,","&A4,""),2,1000)

Thanks for the reply, my full sheet has 27 staff members, the screenshot is of the senior nurses section only, I assume this code could be expanded with extra IF functions to encompass the whole staffing section?

 

[Fluff]

I assume this code could be expanded with extra IF functions to encompass the whole staffing section?

That's right, I only did the 1st 3 to show the general construct.

 

[adams2712]

Managed t

That's right, I only did the 1st 3 to show the general construct.

Managed to get it to work on new rows, code will be very long, but i think its going to work, thanks again

 

[Fluff]

code will be very long

That's why I only did the first few.

Glad to help & thanks for the feedback.

 

[Peter_SSs]

My command is as follows:
=TEXTJOIN(",",TRUE,IF(A6=B2:B27,A2:A23,""))

Are you sure that is your formula? Your ranges (B2:B27 and A2:A23) are different sizes. That means that if, say, B26 = A6 then the formula would return a #N/A error because there is no corresponding value in your column A range.

Assuming that your ranges are the same size then another option to save such a long formula might be to employ a user-defined function.
Here is one you could try. I have built-in the 'ignore empty cells' setting as I assumed you would never want an empty cell returned in the results
I have also hard-coded the comma delimiter but that could also be an argument of the function if required.

If you are interested in trying this approach then to implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function TJ(delim As String, str As String, strFoundInRng As Range, ResultRng As Range) As String
  Dim i As Long
  
  For i = 1 To strFoundInRng.Count
    If UCase(strFoundInRng.Cells(i).Value) = UCase(str) Then TJ = TJ & "," & ResultRng.Cells(i).Value
  Next i
  TJ = Mid(TJ, 2)
End Function

adams2712.xlsm
	A	B	C	D
1
2	aa	a
3	bb	b		aa,dd,ee,gg,jj
4	cc	x
5	a
6	dd	a
7	ee	a
8	ff	j
9	gg	a
10	hh
11		a
12	jj	a
13
Sheet1

Cell Formulas
Range	Formula
D3	D3	=TJ(",",A5,B2:B23,A2:A23)