Formula required please

I have tried an If Statement, but my expertise isn't great.

What i am trying to do on a simple table is to get the summary cell to provide the right code, if a Y is present under one of the headings, but also to add any others with a Y, as in the Example.

If there is a N or N/A then that code is not to be shown.

Any help would be appreciated.

thanks.

I'm not sure if you have the TEXTJOIN function in Excel 2016, but if so you could use this array* formula in G3:

=TEXTJOIN(", ",TRUE,IF(Table1[@[HE]:[7EC]]="Y",C$2:F$2,""))

*NOTE that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter instead of the usual Enter.

Hope this helps.

Pete

Thanks for the reply, however, I'm on 2016 and TextJoin is not available. So i need another option if ones available.

please create TEXTJOIN function by VBA, so copy the following code:

PHP Code: 

Function TextJoin(delimiter As String, ignore_empty As Boolean, ParamArray text() As Variant) As String
    Dim i As Long
    Dim result As String
    
    For i = LBound(text) To UBound(text)
        If ignore_empty And text(i) = "" Then
        Else
            result = result & text(i) & delimiter
        End If
    Next i
    
    If Len(result) > 0 Then
        result = Left(result, Len(result) - Len(delimiter))
    End If
    
    TextJoin = result
End Function 


And save your file with this format *.xlsm. let me know if you have any question.

Ah, not familiar with VBA so thats something really new to me, so not sure what to do with this or how it works.

How about:

Formula:

Please Login or Register  to view this content.

You could use this formula instead of my earlier one:

=SUBSTITUTE(SUBSTITUTE(IF(C3="Y",C$2&", ","")&IF(D3="Y",D$2&", ","")&IF(E3="Y",E$2&", ","")&IF(F3="Y",F$2&", ","")&"x",", x",""),"x","")

Hope this helps.

Pete

Thanks for both options, the latter is the better and works. Thank you.

You're welcome, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

Originally Posted by Pete_UK

You're welcome, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

Thanks will do. Once I’m back on the pc. Can’t see how to do that on the iPhone. However thanks for your help today.