Return the values for the same names across a row of data

[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!

 

[Guest]

Hi,

Try as follow:

first sheet (sheet1):
A B C D E F G
1 Bob Frank Joe Bob Mary Frank Jon
2 X X X
3 X X X
4 X X X X

secod sheet (sheet2):
A B C D E
1 Bob Frank Joe Mary Jon
2
3
4

enter this formula in the cell A2:

=HLOOKUP(sheet2!A$1,Sheet1!$A$1:$G$4,row()-1,FALSE)
copy drag to the rigt to column E and then drag down to row 4

Thanks,

 

[Guest]

Hi Again,

Sorry there is a mistake in the formula the correct formula is:

=IF(HLOOKUP(sheet2!A$1,Sheet1!$A$1:$G$4,row(),FALSE)=0,"",HLOOKUP(sheet2!A$1,Sheet1!$A$1:$G$4,row(),FALSE))

Thanks

 

[Guest]

This only seems to work for the first row. When I copy the formula down it
doesn't work and the cells are blank? I used this formula:
=IF(HLOOKUP(sheet2!A$1,Sheet1!$A$1:$G$4,row(),FALSE)=0,"",HLOOKUP(sheet2!A$1,Sheet1!$A$1:$G$4,row(),FALSE))

 

[Guest]

Hi,

First you have to make sur of $ in the formula i mean don't miss them and
the second thing is that where did you entered this formula i mean in which
row it should enter in the second row otherwise you have to make the number 2
with ROW() function for example if you are entering this for mula in the row
20 you have to enter ROW()-18 instead ROW() which makes the number 2. i test
this formula and it worked.

Thanks,

 

[Guest]

I am not sure what is going on. I am entering the formula in row 4 so I am
inputting ROW ()-2 but it doesn't seem to be working for me.

 

[Guest]

I am starting to input my formula in row H4. Does that make a difference
since I am not starting in column A2?

 

[Guest]

Is there a way you can look at your formula again? I tried this example in
excel and it didn't pull it over correctly, it doesn't look at all the X's
and give me the full answer.