Excel Lists and Duplicate adjacent cells

Ozshock

New Member
Joined
Feb 6, 2019
Messages
1
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top