Complex People Formula Problem :]

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need your expertise with a formula to help with something that sounds simple, but proves very difficult:

There are 20 strangers who will all meet each other and choose preferable business travel buddies. Everyone has a score sheet to fill out either a "YES" or "NO" for each of the 19 people he/she will meet. We want to find all the mutual "YES" responses (ie, it counts when #1 and #18 say "YES" to each other, but not when #1 says "YES" to #18, and #18 marks "NO" to #1)

Is Excel the right software to figure out the matches? So far I've created a large grid with 20 rows and 20 columns, but it feels confusing, redundant, and taxing trying to find out exact matches. There has to be a formula, with easy data entry!

If you can help, I'd appreciate it

Thanks
-EB.
 
Hi
one idea:
1- set upb your spreadsheet like the following:

A B C D
1 name1 name2 name3
2 name1 --- Yes No
3 name2 No Yes Yes
.....

Now select cell B2. Goto 'Format - Conditional Format and enter the
following formula
=AND(B1=OFFSET($A$1,COLUMN()-1,ROW()-1),B1="Yes")
Choose a format

copy this format for all cells in your range. this should highlight all
mutual answers which are 'Yes'
 
Frank, I don't seem to get any results. I went to B2, did the conditional formatting, and tried pasting the conditions to the rest of the cells. No changes occur, even with Mutual Yesses. Am I overlooking something

Thanks

----- Frank Kabel wrote: ----

H
one idea
1- set upb your spreadsheet like the following

A B C
1 name1 name2 name
2 name1 --- Yes N
3 name2 No Yes Ye
....

Now select cell B2. Goto 'Format - Conditional Format and enter th
following formul
=AND(B1=OFFSET($A$1,COLUMN()-1,ROW()-1),B1="Yes"
Choose a forma

copy this format for all cells in your range. this should highlight al
mutual answers which are 'Yes


-
Regard
Frank Kabe
Frankfurt, German


EBWired wrote
 
Hi
typo in the formula. Use the formula
=AND(B2=OFFSET($A$1,COLUMN()-1,ROW()-1),B2="Yes")
 
Back
Top