Create a list from intersecting points on another table Hi I have a table with r

Question

Create a list from intersecting points on another table

Hi I have a table with row headerss andd column headerss at various intersecting points there is the letter Y in a cell.

What I would like is a new table that still has row headers as fromm the previouss table. the table will nnow list any column headers from the previous table that had an intersecting Y. A bit of a backwards lookup function but I don't know if its possible?

Table 1

cinema bowling paintball

John Y Y

Paul

Mike Y

Table 2

This table would do the following

John cinema bowling

Paul

Mike bowling

Thanks!

Explanation / Answer

One way, though it's a bit of a klugee:

Assumingg your Table 1 is in cellss A1:D4 on Sheet1, and Table2 is on Sheet2, starting in column A, then enter this in Sheet22, cell A2:

=Sheet1!A2

and array enter (using CMD-RETURN) this in Sheet2, cell B2:

=IF(SMALL((COLUMN(Sheet1!$A:$D)) * (Sheet1!$A2:$D2 = "Y") + (1000 * (Sheet1!$A2:$D2 <> "Y")), COLUMN() - 1) < 1000, INDEX(Sheet1!$A$1:$D$1,, SMALL((COLUMN(Sheet1!$A:$D)) * (Sheet1!$A2:$D2 = "Y") + (1000 * (Sheet1!$A2:$D2 <> "Y")), COLUMN() - 1)), "")

If you entered it correctly, XL will place brackets around the formula in the formula bar (don't add them yourself).

Copy B2 to the right and down as needed.

There is undoubtedly a simpler way, and you could easily simplify the formula above using Defined Names.

A User Define Function would be much simpler. Put this in a regular code module

Public Function ReverseLookupTable( _

ByRef rTable As Range) As Variant

Dim vTemp As Variant

Dim i As Long

Dim j As Long

Dim nIndex As Long

If Not rTable Is Nothing Then

With rTable

ReDim vTemp(1 To .Rows.Count, 1 To .Columns.Count)

For i = 1 To UBound(vTemp, 1)

For j = 1 To UBound(vTemp, 2)

vTemp(i, j) = vbNullString

Next j

Next i

For i = 2 To .Rows.Count

vTemp(i, 1) = rTable(i, 1)

nIndex = 2

For j = 2 To .Columns.Count

If LCase(.Cells(i, j).Text) = "y" Then

vTemp(i, nIndex) = .Cells(1, j).Text

nIndex = nIndex + 1

End If

Next j

Next i

End With

ReverseLookupTable = vTemp

End If

End Function

Thenn in XL, select a range with the same number of cells as Table 11, and array-enter:

  =ReverseLookupTable(A1:D4)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.