Highlight cells if same word is used in same row

[madforgolf]

Hi
Im trying to run a football killers where if someone selected the same team in future weeks it would highlight it, so they would need to select another team.
table is over 200 rows
i have came across a formula placed in conditional formatting =COUNTIF($F2:$AS201,F2)>1 which is supposed to work across rows, bit its not working for me. It did work ( i think briefly, but then started highlighting teams in different rows

Con Format so it goes red if double used.
not sure if i have made a mistake on how its entered in conditional formatting

thanks for looking , any help or better way to highlight please let me know.
thanks
Marty

 

[6StringJazzer]

I don't see a problem with that formula, other than that would use a $ for $AS$201. However, that wouldn't keep it from working the way you want.

What is in all those columns F:AS? Is it all team names? Are you looking for the same pick in the same column or in any column?

I suspect your requirement is not that clear. Could you post a small example of the data, pointing out the result you would like to see?

Here is a simplified example using your formula, which highlights when a pick is repeated in any column.

$scratch.xlsm
	F	G	H	I
1
2	Dallas Cowboys	Miami Dolphins	Atlanta Falcons	Miami Dolphins
3	Buffalo Bills	San Francisco 49ers	Pittsburgh Steelers	Denver Broncos
4	San Francisco 49ers	New York Jets	Tennessee Titans	Los Angeles Chargers
5	Tampa Bay Buccaneers	Cincinnati Bengals	Denver Broncos	Tennessee Titans
6	Baltimore Ravens	Arizona Cardinals	Tennessee Titans	Chicago Bears
7	Buffalo Bills	Cincinnati Bengals	Denver Broncos	Indianapolis Colts
8	Cleveland Browns	Atlanta Falcons	Buffalo Bills	Minnesota Vikings
9	Washington Commanders	Los Angeles Rams	Atlanta Falcons	Atlanta Falcons
10	Seattle Seahawks	Baltimore Ravens	Houston Texans	Los Angeles Chargers
11	Green Bay Packers	Philadelphia Eagles	Seattle Seahawks	Pittsburgh Steelers
12	Cincinnati Bengals	Las Vegas Raiders	Minnesota Vikings	Baltimore Ravens
13	New Orleans Saints	Dallas Cowboys	Carolina Panthers	Baltimore Ravens
14	Jacksonville Jaguars	San Francisco 49ers	Baltimore Ravens	Pittsburgh Steelers
15	Los Angeles Chargers	Miami Dolphins	Los Angeles Rams	Cincinnati Bengals
Teams any column

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:AS201	Expression	=COUNTIF($F2:$AS$201,F2)>1	text	NO

Here is the same data with a different rule that highlights only when a pick is repeated later in the same column:

$scratch.xlsm
	F	G	H	I
1
2	Dallas Cowboys	Miami Dolphins	Atlanta Falcons	Miami Dolphins
3	Buffalo Bills	San Francisco 49ers	Pittsburgh Steelers	Denver Broncos
4	San Francisco 49ers	New York Jets	Tennessee Titans	Los Angeles Chargers
5	Tampa Bay Buccaneers	Cincinnati Bengals	Denver Broncos	Tennessee Titans
6	Baltimore Ravens	Arizona Cardinals	Tennessee Titans	Chicago Bears
7	Buffalo Bills	Cincinnati Bengals	Denver Broncos	Indianapolis Colts
8	Cleveland Browns	Atlanta Falcons	Buffalo Bills	Minnesota Vikings
9	Washington Commanders	Los Angeles Rams	Atlanta Falcons	Atlanta Falcons
10	Seattle Seahawks	Baltimore Ravens	Houston Texans	Los Angeles Chargers
11	Green Bay Packers	Philadelphia Eagles	Seattle Seahawks	Pittsburgh Steelers
12	Cincinnati Bengals	Las Vegas Raiders	Minnesota Vikings	Baltimore Ravens
13	New Orleans Saints	Dallas Cowboys	Carolina Panthers	Baltimore Ravens
14	Jacksonville Jaguars	San Francisco 49ers	Baltimore Ravens	Pittsburgh Steelers
15	Los Angeles Chargers	Miami Dolphins	Los Angeles Rams	Cincinnati Bengals
Teams same column

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:AS201	Expression	=COUNTIF(F2:F$201,F2)>1	text	NO

 

[6StringJazzer]

I just looked at your title, which says you want to see if a name occurs in the same row which is not remotely what your formula does.

If you want to highlight names when they are repeated to the right in the same row (still using the same sample data)

$scratch.xlsm
	F	G	H	I
1
2	Dallas Cowboys	Miami Dolphins	Atlanta Falcons	Miami Dolphins
3	Buffalo Bills	San Francisco 49ers	Pittsburgh Steelers	Denver Broncos
4	San Francisco 49ers	New York Jets	Tennessee Titans	Los Angeles Chargers
5	Tampa Bay Buccaneers	Cincinnati Bengals	Denver Broncos	Tennessee Titans
6	Baltimore Ravens	Arizona Cardinals	Tennessee Titans	Chicago Bears
7	Buffalo Bills	Cincinnati Bengals	Denver Broncos	Indianapolis Colts
8	Cleveland Browns	Atlanta Falcons	Buffalo Bills	Minnesota Vikings
9	Washington Commanders	Los Angeles Rams	Atlanta Falcons	Atlanta Falcons
10	Seattle Seahawks	Baltimore Ravens	Houston Texans	Los Angeles Chargers
11	Green Bay Packers	Philadelphia Eagles	Seattle Seahawks	Pittsburgh Steelers
12	Cincinnati Bengals	Las Vegas Raiders	Minnesota Vikings	Baltimore Ravens
13	New Orleans Saints	Dallas Cowboys	Carolina Panthers	Baltimore Ravens
14	Jacksonville Jaguars	San Francisco 49ers	Baltimore Ravens	Pittsburgh Steelers
15	Los Angeles Chargers	Miami Dolphins	Los Angeles Rams	Cincinnati Bengals
Teams same column

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:AS201	Expression	=COUNTIF(F2:$AS2,F2)>1	text	NO

 

[madforgolf]

I just looked at your title, which says you want to see if a name occurs in the same row which is not remotely what your formula does.

If you want to highlight names when they are repeated to the right in the same row (still using the same sample data)

$scratch.xlsm
	F	G	H	I
1
2	Dallas Cowboys	Miami Dolphins	Atlanta Falcons	Miami Dolphins
3	Buffalo Bills	San Francisco 49ers	Pittsburgh Steelers	Denver Broncos
4	San Francisco 49ers	New York Jets	Tennessee Titans	Los Angeles Chargers
5	Tampa Bay Buccaneers	Cincinnati Bengals	Denver Broncos	Tennessee Titans
6	Baltimore Ravens	Arizona Cardinals	Tennessee Titans	Chicago Bears
7	Buffalo Bills	Cincinnati Bengals	Denver Broncos	Indianapolis Colts
8	Cleveland Browns	Atlanta Falcons	Buffalo Bills	Minnesota Vikings
9	Washington Commanders	Los Angeles Rams	Atlanta Falcons	Atlanta Falcons
10	Seattle Seahawks	Baltimore Ravens	Houston Texans	Los Angeles Chargers
11	Green Bay Packers	Philadelphia Eagles	Seattle Seahawks	Pittsburgh Steelers
12	Cincinnati Bengals	Las Vegas Raiders	Minnesota Vikings	Baltimore Ravens
13	New Orleans Saints	Dallas Cowboys	Carolina Panthers	Baltimore Ravens
14	Jacksonville Jaguars	San Francisco 49ers	Baltimore Ravens	Pittsburgh Steelers
15	Los Angeles Chargers	Miami Dolphins	Los Angeles Rams	Cincinnati Bengals
Teams same column

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:AS201	Expression	=COUNTIF(F2:$AS2,F2)>1	text	NO

Hi 6StringJazzer for the reply. sorry about the long wait in replying

i have revistied your comments and noticed when i entered your formulas i stiil had issues. Then i noticed i should have use =countifs (i missed the S) so now it is working as it should.

thank you for taking the time to responded to my query

regards

Marty

 

[6StringJazzer]

Glad to help!