You don't need a macro.

Copy the worksheet to a new name (don't break the original)

If you have other stuff in that table data area (B2:....), then make sure those
cells are empty (use a bunch of edit|replaces???)

Now select B2:thebottomrightcornercell
Edit|goto|special|Constants
and hit enter

Notice that just the cells that have Yes in them are selected.

One cell will be the activecell in that selection. I'm gonna guess it's in
column B, but change this formula to match that activecell's column.

Type this:
=b$1
and hit ctrl-enter to fill all the cells with the appropriate formula (excel
will adjust the formula for each column).

Now select that range again:
B2:thebottomrightcornercell

Edit|goto|special|blanks
And only the blank cells will be selected
Edit|delete|shift cells left

And clean up the headers in row 1.




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).

--

Dave Peterson