How can I find all cell references in a row that contain a value, and display them in one cell?

Question

The Problem:

I have an Excel sheet that I am using for a fairly large requirements traceability matrix using the columns for QA test case numbers, and the rows for the requirements. I have provided the ability show the number of test cases that have been used against a single requirement by having a cell next to the req using a COUNTIF for "x" for every row. While this will show the number of tests done against that requirement, it does NOT show the actual QA test case number used. This sometimes requires the user to scroll left/right to find the exact test case numbers (in the column headers) that have been used against that requirement.

• For example: Req 2.1.1 is showing "5" as the number of test cases used against it. The user must scroll right until finding all the X's in a cell, and write down/remember the QA number in the column it is in.

I've included a screenshot to help show what I mean.

What I'm Looking For:

I'd like to add a column next to "E" (one that counts the x's) that will display every QA test case number (column headers G5:HR5) that has an "X" for a given requirement/row.

Ex: Requirement 1.1.2 has four x's in that row, indicated four different test cases used for this requirement (QA-466, QA-467, QA-468, and QA-470). I'd like to have a cell somewhere in the row with the 1.1.2 requirement display "QA-466 QA-467 QA-468 QA-470".

I'm having a feeling that I'll need more than a few hidden cells to run some formulas to make this work, and that I'd be using REFERENCE and OFFSET at some point...but where and how?

Answer 1

If you have OFFICE 365 Excel, use TEXTJOIN() as an array formula in F9:

=TEXTJOIN(" ",TRUE,IF(G9:HR9="x",$G$5:$HR$5,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.

Small example:

---

NOTE

TEXTJOIN was introduced in OFFICE 365 Excel.

If you do not have TEXTJOIN function then put the following in a module attached to the workbook. And use the formula as described above:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN2 = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function