Lookups maybe

[thermometer]

I thought I could use a lookup function to do this, but I'm stumped.
Here's the situation. Let's say in col A there are names. In col b -
h there are club affiliations. A person in col a can belong to many
clubs and they are designated with an X. Here's what it might look
like:

Name Panthers Giants Aces Javas Kings
Joe X X
Mary X X X
Judy X X X
Sam X X
Tom X X
etc.

How can I get an individual list of each name to see what clubs they
belong to? This would help me pinpoint people who do not belong to a
club. Conversely, a list of clubs with members would be beneficial
also. Any help is appreciated.

 

[Guest]

You could try auto filter and then select the x from which ones you want to
see.

 

[Dave Peterson]

I think I'd create a new worksheet.

Then select the original table
Edit|copy
select A1 of the new worksheet
edit|paste special|check Transpose and click ok.

Then apply Data|Filter|autofilter to that new worksheet.

I could filter to show just the rows that contain X in the Joe column (say).

This technique won't work if the number of rows is more than the the number of
columns that excel uses. (In xl2003 and below, I'm limited to 256 columns. In
xl2007, I can use 16k columns.)

 

[Pete_UK]

You could probably do this with a couple of User-defined functions
(UDF). Have your table on one sheet and in another sheet enter a name,
eg in A1. A UDF in B1 could find where the name exists in the table
and then output a string of group names if there is an X in the column
for that row. Similarly, enter a group name in A3 and another
(similar) UDF would find which column that relates to in the main
table and output names if an X appears in that column, as appropriate.

Hope this helps.

Pete