I am trying to create a list of column names based on specific criteria (if the cell contains "*").
Basically, I am trying to get Excel to automatically create the fourth column:
(1) A B C List
(2) Bob* Mike John* A; C
(3) Jane Lisa* Brenda* B; C
*Please note that I have over 100 columns
I am a little familiar with VBA and the Concatenate function but I'm no expert.
Thanks for taking the time to read this post!
Irene
Multiple options really:
Option 1: Excel TEXTJOIN
If you have an Excel license supporting TEXTJOIN() you could use:
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),A2:C2,""))
For returning values or:
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),$A$1:$C$1,""))
For returning the column headers. Enter both formulas through CtrlShiftEnter and drag down.
Option 2: Google Spreadsheet TEXTJOIN
If you can use Google Spreadsheets (free to use, and if you just need to do this operation once it might be worthwhile to transfer your data) she same functions are available. It will look like:
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),A2:C2,"")))
For returning values or:
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("~*",A2:C2)),$A$1:$C$1,"")))
For returning column headers again.
Notice in both Option 1 and Option 2 I have used a tilde, ~, as an escape character telling excel we literally looking for an asterisk.
Option 3: Visual Basics
If you don't have an Excel license supporting TEXTJOIN() and using Google Spreadsheets is no option either, your best bet is an UDF (User Defined Function), for example:
Function CONCATENATEIF(RNG As Range, CON As String) As String
For Each CL In RNG
If InStr(1, CL.Value, CON) > 0 Then CONCATENATEIF = CONCATENATEIF & CL.Value & ", "
Next CL
If CONCATENATEIF <> "" Then
CONCATENATEIF = Left(CONCATENATEIF, Len(CONCATENATEIF) - 2)
End If
End Function
Call it in your worksheet like:
=CONCATENATEIF(A2:C2,"*")
And drag down... (notice this time we don't need the tilde). Likewise, if you want to return the headers simply change CONCATENATEIF = CONCATENATEIF & CL.Value & ", " into CONCATENATEIF = CONCATENATEIF & Cells(1, CL.Column).Value & ", "
Output for all options:
Or:
