hi guys, im running a tournament and i have alist of names in excel...and as per our rounds we dont like people playing the same person twice, so matchups are made for round 1, then we get our scores in and determine who plays who in the next round...generally the top scoring players will play each other and in 4 games we get a winner
in a spreadsheet that someone made for us in openoffice the formula reads thus...
=IF(AND(C3="-";D3="-");"";IF(OR(COUNTIF($C$3:$D$928;C3)>1;COUNTIF($C$3:$D$928;D3)>1);"Duplicate Match";""))
basically if noone is elected from the list it displays ""
if 2 people have already been selected to play in an earlier round it will display "Duplicate Matchup"
im having lots of trouble trying to get this converted to MS Excel on windows 10, ill try to explain my issues below
Round Error Message
1 player a vs player c
1 player b vs player d
1 player e vs player f
2 Player a vs Player b
2 player c vs player e
2 player d vs player f
3 player c vs player a (should display "duplicate matchup" in cell opposite)
what i have currently is a countif formula but its counting the amount of times a single player appears in the lists, and if they appear more than once then it display the error message, but i only want it to display the error if both cels appear again a second time.
this is my formula currently that displays the error if a player turns up twice:
=IF(AND(C3="-",D3="-","", (only displays " " if nothing selected)
IF(OR(COUNTIF($C$3:$D$450,C3)>1,COUNTIF($C$3:$D$450,D3)>1),"DUPLICATE",""))
(the C3-D450 is the range of the selections, and C3 is the first one i dont want to have duplicates of, along with D3 in the second part...it looks as if i need a whole new section to count if those 2 appear
so the AND formula seems to be the one that should be there instead of the OR part, but i cant get it working...any help?
in a spreadsheet that someone made for us in openoffice the formula reads thus...
=IF(AND(C3="-";D3="-");"";IF(OR(COUNTIF($C$3:$D$928;C3)>1;COUNTIF($C$3:$D$928;D3)>1);"Duplicate Match";""))
basically if noone is elected from the list it displays ""
if 2 people have already been selected to play in an earlier round it will display "Duplicate Matchup"
im having lots of trouble trying to get this converted to MS Excel on windows 10, ill try to explain my issues below
Round Error Message
1 player a vs player c
1 player b vs player d
1 player e vs player f
2 Player a vs Player b
2 player c vs player e
2 player d vs player f
3 player c vs player a (should display "duplicate matchup" in cell opposite)
what i have currently is a countif formula but its counting the amount of times a single player appears in the lists, and if they appear more than once then it display the error message, but i only want it to display the error if both cels appear again a second time.
this is my formula currently that displays the error if a player turns up twice:
=IF(AND(C3="-",D3="-","", (only displays " " if nothing selected)
IF(OR(COUNTIF($C$3:$D$450,C3)>1,COUNTIF($C$3:$D$450,D3)>1),"DUPLICATE",""))
(the C3-D450 is the range of the selections, and C3 is the first one i dont want to have duplicates of, along with D3 in the second part...it looks as if i need a whole new section to count if those 2 appear
so the AND formula seems to be the one that should be there instead of the OR part, but i cant get it working...any help?