• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare columns from 2 different sheets and return info needed from another cell

L

Lynda

New Member
Please help:

I have a sheet like this with over 100 lines of names:

Room1 Room2 Room3 Room4
David x x
Sally x x x


How do I write a formula that will show me only the rooms for that particular person? I've tried Vlookup and Hlookup and my own version of If/Then but I can't figure it out.

This is what I need to show

David Room1 Room3
Sally Room2 Room3 Room4

Thank you in advance for all your help.

Lynda
 
Shoot - that table didn't post correctly. The 'x' should like up under the room that person is going to be in.

-----------------------------------------------------
I have a sheet like this with over 100 lines of names:

Room1 Room2 Room3 Room4
David x x
Sally x x x


How do I write a formula that will show me only the rooms for that particular person? I've tried Vlookup and Hlookup and my own version of If/Then but I can't figure it out.

This is what I need to show is that david will have rooms 1 and 3 and Sally will have 2, 3, and 4.

David Room1 Room3
Sally Room2 Room3 Room4

Thank you in advance for all your help.

Lynda[/quote]
 
Hi Excel Ninja --

It is in a table already. Guess that doesn't come across in my post.
The first column has the persons name, the next 25 columns are title with room names, but that person is only assigned to three of the rooms, so of those 25 columns there are only three that are selected by an X for this particular person.
When I tried the filtering idea - I got to a column that didn't have but one X in it and it filtered out all the others when I removed all the blanks.
Oh - but now that I type this - I can try finding the one column that has the most Xs and filter that one first, then go to the next most Xs and filter that, and so on. Oh that may work. I'll try that.

Thanks,
Lynda
 
So I'm looking for the result to be -- person's name and the three different room names that for that person.
 
Lynda

If your data is in tables then use the tables to make a pivot table, as you add to your raw data tables the pivot will update on refresh.
 
You must log in or register to reply here.
Back
Top