=CONCATENATE problem

Could you attached sample file as on picture, please?
Let's try:
As I see you've got excel2017, so you don't have a TEXTJOIN function, then use this macro:

Public Function textjoinSUB(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
    
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
    
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty
            If Len(DataIndex) > 0 Then
                'Found the item isn't empty, check if user specified bUnique as True
                If bUnique = True Then
                    'bUnique is true, check if the item has been included in the result yet
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                        'Item has not been included in the result, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                Else
                    'bUnique is not true, add item to the result
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
    
    'Output result
    textjoinSUB = strResult
    
End Function

and into C2:

=textjoinSUB(IF(A2:A10="Spelling",B2:B10,""),", ",1)

and into D2:

=textjoinSUB(IF(A2:A10="Addition",B2:B10,""),", ",1)

both formulas have to be accepted with Ctrl+Sfhift+Enter as both are array formulas.

Should works.

Originally Posted by Tarqish

=textjoinSUB(IF(A2:A94="Spelling",B2:B94,""),", ",1) (Your version)
=textjoinSUB(IF(A2:A94=C1,B2:B94,""),", ",1) (What I need - C1 needs to pull through the word "spelling" to complete the formula)

Are you sure that you accept formulas with Ctrl+Shift+Enter after you've changed it into C1?
I don't have any problems with it.

Capture.JPG

You welcome.
If you happy with solution, please use Thread tools and mark thread as SOLVED.

Sorry, one more question before I put the thread as solved if I may KOKOSEK and sorry to ask what may be a very simple question:

I need to be able to check another field to return a slightly altered list. Basically produce the list we have already done in Cell D4 and then list those who have "Yes" in column C in E4. My If/And statement does not yield a result so I am missing something.

I have attached another picture.

2.png

If you prefer me to create a a new post, please let me know.

Thanks
Tarq

Into E3 (those trained)

Formula:

=textjoinSUB(IF((A3:A8=D1)*(C3:C8="Yes"),B3:B8,""),", ",1)

of course accepted by Ctrl+Shift+Enter.

Capture.JPG