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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.