String concatenation over more than a few cells is best left to a VBA User Defined Function (aka UDF) even without setting criteria. Your situation of "nesting of a hundred "IF" functions" would certainly put it in this category.
Tap Alt+F11 and when the VBE opens, immedaitely use the pull-down menus to Insert ? Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).
Public Function conditional_concat(rSTRs As Range, rCRITs As Range, Optional sDELIM As String = ", ")
Dim c As Long, sTMP As String
For c = 1 To Application.Min(rSTRs.Cells.Count, rCRITs.Cells.Count)
If CBool(rCRITs(c).Value2) Then _
sTMP = sTMP & rSTRs(c).Value & sDELIM
Next c
conditional_concat = Left(sTMP, Application.Max(Len(sTMP) - Len(sDELIM), 0))
End Function
Tap Alt+Q to return to your worksheet. Use this UDF like any native Excel worksheet function. The syntax is,
conditional_concat(<range of strings>, <range of conditions>, [optional] <delimiter as string>)
??????
The formula in G2 is,
=conditional_concat(A$1:E$1, A2:E2)
Fill down as necessary.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…