PDA

View Full Version : Index/Return cell references from table array based on "Yes"/"No" response



tatendamark
01-18-2018, 06:57 AM
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
01-18-2018, 07:52 AM
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
01-18-2018, 08:05 AM
Yes, exactly that.

georgiboy
01-18-2018, 08:09 AM
Does it have to look at your headers or do you want it to look at excels cell references?

georgiboy
01-18-2018, 09:02 AM
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("Yes",B2:B6,0))))),4),1,""))

Hope this helps

tatendamark
01-19-2018, 12:05 AM
So I get an N/A response with the formula you suggested. File attached.

georgiboy
01-19-2018, 03:27 AM
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
01-19-2018, 03:45 AM
Awesome. So the formula works, but I would like it to look at the headers, and not the cell references.

georgiboy
01-19-2018, 05:29 AM
Try this in B9:

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

Hope this helps

tatendamark
01-19-2018, 05:36 AM
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
01-19-2018, 06:29 AM
How about this:

21400

tatendamark
01-19-2018, 06:42 AM
This would be perfect, except I need each "header" to be in a separate cell, as they will be referenced elsewhere.

Paul_Hossler
01-19-2018, 07:26 AM
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
01-19-2018, 07:32 AM
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
01-22-2018, 06:25 AM
Awesome! What a genius! Thank you very much!