TEXTJOIN Alternative

[mightymo77]

Hey guys. I was reading through some posts and everyone here seems very helpful. I have this formula below, however it's not working on my work machine which uses Excel 2016. Can anyone create an alternative for me that will function in Excel 2016? I would appreciate any help!

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"BLACK,BLUE")),$F$1:$T$1,""))

 

[JEC]

Map1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	6	7	8	9	0	11	22	33	44	55	66	77	88	99	110
2	Hi BLACK,BLUE	Hi BLACK,BLUE	Black	blue	test	this is BLACK,BLUE						this is BLACK,BLUE
3
4
5	6, 7, 11, 77
Blad1

Cell Formulas
Range	Formula
F5	F5	=jec(F2:T2)

Not sure but still think this is what you mean

Function jec(searchRng As Range) As String
 Dim it As Variant, c00 As String
 Application.Volatile
 
  For Each it In searchRng
    If InStr(it, "BLACK,BLUE") Then c00 = c00 & ", " & it.Offset(-1).Value
  Next
  jec = Mid(c00, 3)
End Function

 

[Fluff]

But compare that to the OP's formula

+Fluff 1.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	6	7	8	9	0	11	22	33	44	55	66	77	88	99	110
2	Hi BLACK,BLUE	Hi BLACK,BLUE	BLUE	BLACK	test	this is BLACK,BLUE	Black	Blue				this is BLACK,BLUE
3
4
5	6, 7, 11, 77
6	8, 9, 44, 55, 66, 88, 99, 110
Main

Cell Formulas
Range	Formula
F5	F5	=jec(F2:T2)
F6	F6	=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"BLACK,BLUE")),$F$1:$T$1,""))

 

[mightymo77]

JEC when I enter your VBA code, then enter =jec(F2:T2) in X2, I am getting #NAME?

 

[JEC]

Yes but I have a strong feeling that the OP's formula is not what he/she desires. The other way around is more logical, to me at least

 

[JEC]

Did you right click to insert a new Module?

 

[Fluff]

@mightymo77 Looking at post#12 which cell is giving the expected result F5, F6 or neither?

 

[mightymo77]

I don't know. Let me try to explain it differently. Here is my csv's columns A - W:

First Name

Last Name

User Email

Device Name

Risk

Firewall

Peer to Peer

Antivirus

Encryption

WiFi

Passcode

Password Policy

Antispyware

OS Auto Update

Disk Encryption

Company Name

Date Enrolled

Last Connected

Autolock

OS Up-to-Date

OS Name

OS Architecture

OS Version

BRITT

CLINE

BRITT.CLINE@email.com

BRITT-T550(9664)

800​

PASS

PASS

PASS

NA

PASS

NA

PASS

WARNING

PASS

PASS

Acme

11.02.2021 14:25 GMT

12.15.2021 10:26 GMT

NA

NA

Windows 10 Pro

64​

10.0.19043

JAN

SMITH

JAN.SMITH@email.com

JANiPhone.local(9665)

800​

NA

NA

NA

PASS

WARNING

PASS

NA

NA

NA

NA

Acme

11.02.2021 14:37 GMT

12.03.2021 20:43 GMT

WARNING

PASS

iOS

x64

15.1.1

JANET

LACY

janet.LACY@email.com

T550(9666)

800​

PASS

PASS

PASS

NA

PASS

NA

PASS

WARNING

PASS

FAIL

Acme

11.03.2021 17:41 GMT

12.15.2021 01:23 GMT

NA

NA

Windows 10 Pro

64​

10.0.19043

What I am needing are the names of the FAIL and WARNING titles to be displayed in column X. For example X2 should read Antispyware, X3 should read WiFi, Autolock and X4 should read Antispyware, Disk Encryption.

Sorry if there was any confusion. Does this make more sense? I cannot use the TEXTJOIN unfortunately because I am using Excel 2016 on my work machine.

 

[JEC]

Then like this

Function jec(searchRng As Range, mergeRng As Range) As String
 Dim it As Variant, ar As Variant, i As Long, c00 As String
 Application.Volatile
 
 ar = searchRng
 For i = 1 To UBound(ar, 2)
    If ar(1, i) = "WARNING" Or ar(1, i) = "FAIL" Then c00 = c00 & ", " & mergeRng(1, i)
 Next
 jec = Mid(c00, 3)
End Function

=jec(F2:T2;$F$1:$T$1)

 

[JEC]

You can remove Dim it as variant, I forgot

 

[mightymo77]

JEC I opened the VBA window, created a second module window, and pasted this in:

Function jec(searchRng As Range, mergeRng As Range) As String
ar As Variant, i As Long, c00 As String
Application.Volatile

ar = searchRng
For i = 1 To UBound(ar, 2)
If ar(1, i) = "WARNING" Or ar(1, i) = "FAIL" Then c00 = c00 & ", " & mergeRng(1, i)
Next
jec = Mid(c00, 3)
End Function

I then closed those two windows to bring me back to the csv file, and pasted this in X2:
=jec(F2:T2;$F$1:$T$1)

I am getting "There's a problem with this formula" error. Am I doing something wrong?