TRY:


Sub Summarise()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long, irow As Long
Dim icol As Integer, jcol As Integer

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Searchable Data")

col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
.Cells(1, 1).Resize(Lastrow, 1).Copy ws2.Cells(1, 1)

For irow = 2 To Lastrow
jcol = 1
For icol = 2 To 31
If .Cells(irow, icol) = "Yes" Then
jcol = jcol + 1
ws2.Cells(irow, jcol) = .Cells(1, icol)
End If
Next icol
Next irow

End With


End Sub

HTH


"Alan" wrote:

I'm trying to find a solution to my problem and I'm pretty sure its
gonna have to be in VBA (hoping its a simple answer). The relevant
information is as follows:

I have 1 worksheet (called "Data") with 412 managers in column A and
30 funds in row 1 (each fund has its own worksheet in the workbook and
the data gets pulled into this summary worksheet called "Data"). If
one of the managers in Column A can be found in one of the Funds in
row 1, there is a "Yes" in the corresponding cell.


Fund 1 Fund 2 Fund 3 Fund 4
Fund 5 etc...
Manager 1 Yes Yes
Manager 2 Yes
Manager 3 Yes
Yes Yes
Manager 4 Yes Yes
Manager 5
etc...


I am trying to make a summary worksheet (called "Searchable Data")
that will have all the same Managers in Column A. However, instead of
having Yesses in cells B2, E2, etc, I want to have the Fund names in
Cells B2, C2, etc like below:


Manager 1 Fund 1 Fund 4
Manager 2 Fund 2
Manager 3 Fund 3 Fund 4 Fund 5
Manager 4 Fund 1 Fund 2
Manager 5
etc...

If I use the lookup formula in excel, it only picks up the first
reference. Trying to have it so that if the first reference was
picked up in Cell B2, then cell B3 picks up the next reference (the
next fund that has that corresponding manager).

Thanks in advance for all your help (I'm hoping its a simple answer).