Hlookup with multiple same values?

[Guest]

Microsoft 2003 - I have a row of data that contains user names. Below this
row is an "x" in each cell that meets a certain critieria that I already
filled out. Now I want to combine all the names in the first row so there is
only one name, not multiples of the same name, and insert the appropriate "X"
below their name based on the entire spreadsheet I put together.

Example below of how it looks now:

Bob Frank Joe Bob Mary Frank Jon
X X X
X X X
X X X X


So I would like it to look like this:

Bob Frank Joe Mary Jon
X X X
X X X
X X X X


Thanks! I had posted this earlier but the formula given did not work.

 

[Dave Peterson]

Can you get a list of unique names and plop that into a new sheet.

I put my list of 4 names in Sheet2 A11
Then in sheet2!A1, I put this formula:
=IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))>0,"X","")

And drag down as far as necessary (same number of rows as in Sheet1).

Now comes the fun part.

Drag the formula in A2 to B2
The formula in B2 will look like:
=IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))>0,"X","")
(no change at all)

Your job will be to change that $a$1 to $b$1 so it looks like:
=IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$B$1),--(Sheet1!$A2:$G2="X"))>0,"X","")
then drag down that column B.

Then do this same thing for each of the remaining columns.

 

[Guest]

This works great! Thanks! I have one question though, at the end you
mentioned the fun part is "Your job will be to change that $a$1 to $b$1 so it
looks like:" Why not just put a$1 so then you don't have to change the
letter each time the column changes? I did that and it seems to work, I was
just checking to see if there is something I am not seeing/missing? Then you
don't have to go and change each formula.

 

[Dave Peterson]

You're not missing something. I just screwed up.

Glad you saw the better solution.