Index/Return cell references from table array based on "Yes"/"No" response

[tatendamark]

I would like a formula that returns the column and row name, based on "Yes"/"No" response in a table array. Please see below. Basically, I would like a formula that automatically returns the values in the last row below (excel attached):

SCENARIO	A	B	C	D	E
1	Yes	No	No	No	No
2	No	No	No	Yes	No
3	No	Yes	Yes	No	No
4	No	No	No	No	Yes
5	No	No	No	No	No
	Return the following scenarios, based on above "Yes" response
Scenarios	1A	2D	3B	3C	4E

[georgiboy]

Just to be clear,

You want to look in a range for the word "Yes" and if found return: row number and column letter formatted the way you have above, "1A" for example?

[tatendamark]

Yes, exactly that.

[georgiboy]

Does it have to look at your headers or do you want it to look at excels cell references?

[georgiboy]

Lets get the ball rolling then

Try putting this in range B9

=CONCATENATE(ROW(INDIRECT(CELL("address",INDEX(B2:B6,MATCH("Yes",B2:B6,0))) )),SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(CELL("address",INDEX(B2:B6,MATCH("Y es",B2:B6,0))))),4),1,""))

Hope this helps

[tatendamark]

So I get an N/A response with the formula you suggested. File attached.

[georgiboy]

If you look at the second "Yes" in the formula there is a space that should not be there.

It looks like "Y es", remove that space and all should be fine.

[tatendamark]

Awesome. So the formula works, but I would like it to look at the headers, and not the cell references.

[georgiboy]

Try this in B9:

=B1&INDIRECT("A"&MATCH("Yes",B2:B6,0)+1)

Hope this helps

[tatendamark]

Awesome. This works. But there's a possibility of the table containing up to 10 "Yes" responses. Is it possible to return the headers for 10 responses? Excel sheet with example attached.

[georgiboy]

How about this:

Return Scenarios v2.xlsm

[tatendamark]

This would be perfect, except I need each "header" to be in a separate cell, as they will be referenced elsewhere.

[Paul_Hossler]

Using a Sub and not a Function

Option Explicit

Sub drv()
    Call Yes(ActiveSheet.Range("A1:F6"), ActiveSheet.Range("A10"))
End Sub

Sub Yes(YesRange As Range, OutputRange As Range)
    Dim r As Long, c As Long
    Dim A As Variant
    Dim s As String
    
    A = YesRange.Value
    
    For r = 2 To UBound(A, 1)
        For c = 2 To UBound(A, 2)
            If UCase(Trim(A(r, c))) = "YES" Then
                s = s & A(r, 1) & A(1, c) & ","
            End If
        Next c
    Next r
    
    A = Split(s, ",")
    
    OutputRange.Cells(1, 1).Resize(1, UBound(A)).Value = A
    
End Sub

[georgiboy]

cool,

I was working on a sub that placed them transposed compared to yours Paul.

Paul's code will be the best route forward for you i would imagine but i will paste below what i was working on for reference only.

Sub GetRef()    Dim colRange As Range, cCell As Range, rCell As Range, x As Long
    
    Set colRange = Range("B1:F1").Cells ' range of headers
    
    For Each cCell In colRange.Cells
        x = 9 ' row you want results to start from
        For Each rCell In Range(Cells(cCell.Offset(1, 0).Row, cCell.Column), Cells(cCell.End(xlDown).Row, cCell.Column))
            If UCase(rCell.Value) = "YES" Then
                Cells(x, cCell.Column).Value = Cells(rCell.Row, 1).Value & Cells(1, rCell.Column).Value
                x = x + 1
            End If
        Next rCell
    Next cCell
End Sub

Something to note: my code assumes there are no blank cells in your data as it uses the end function.


Hope this helps

[tatendamark]

Awesome! What a genius! Thank you very much!