Automatted Football Tipping

[Tommo]

Hi all,
I am trying to set up a spreadsheet for our works football tipping competition, I want it to automatically Identify who has tipped the same across all the games for that week and to enter the comment - Can't Win and put the name of the person who has tipped the same in column B, I have attached a sample spreadsheet of what the end result should be.

NOTE: the spreadsheet should have nine columns after the names because as of next year there will be 18 teams.

Also there is currently 16 names - I will need at least 20 names please.

I hope someone can help!!!!

Regards
Frustrated - with formulas

Football Tipping - Copy.xlsx

[Kimberly]

I'm not sure I understand your sample correct answers, so this might not be what you're after. (I don't know why there is an answer (Sam E, Kylie) and an answer (Sam E, Kirsten)), but

In cell L4:
=D4&E4&F4&G4&H4&I4&J4&K4
Copy down

In some column in row 4:
=IF(COUNTIF($L$4:$L$19,L4)>1,"Can't Win ("&C4&")","")
Copy down

[Tommo]

Kimberly,

Thanks for your reply, you have almost got it, to explain why the end result should be - Can't Win (Sam.E, Kylie) next to Kirsten's name is it shows everyone that Kirsten, Sam.E & Kylie have all picked the same teams making them unable to win that week, the way you have got it working is the same name against that person but it should only say the other person or persons who has the same tips whether it be 1, 2, 3 or more people.

One other thing I noticed is if a person is away and does not put their tips in then the cell where it says can't Win etc should be blank where as if I leave a persons tips out it still shows - Can't Win (Mike) - should be a blank cell.

I look forward to your reply
Mike.

[Kimberly]

I won't say what you want is impossible, but it sure isn't practical. I suggest making a pivot table. (perhaps make the row fields be the concatenation and under that, the name field). To create a report with the structure you desire, you can use the information from the pivot to manually type the "Can't win" cells.

[Tommo]

Hi Kimberly,

Yes it is possible, I have attached a working copy with all the formulas in it on sheet 1 and sheet 2, when I add new names in column C sheet 1 and then drag down formulas on sheet 1 & sheet 2 to include these new names the formulas fail, it comes up in column B sheet 1 and column D sheet 2 as #value.

Note: on sheet 1 Column B it shows Can't win and 2 names next to Murray - these 2 names have picked the same as Murray.

I look forward to hear from you or anyone else who might be able to help!
regards

Mike