Simple Formula?

[pauldaddyadams]

Hi,

I have a problem which I need someone to look at. I have a list of 1
teams and I need a formula to check the data and for it to identify a
error message if more than two of the same teams are listed.

e.g.
Arsenal
Aston Villa
Liverpool
Chelsea
Arsenal
Wigan
West Brom
Arsenal
Arsenal
Birmingham
Liverpool

I would need a cell to highlight the error that there are two man
arsenal entries as only two are allowed

Can anyone help?

Regards

Pau

 

[aristotle]

Hi,

Assume the entries are within A2:A20

e.g. B2=IF(COUNTIF($A$2:A2,A2)>1,"Dup","Ok")
and copy down to B20

Regards,
A

 

[RagDyeR]

Believe you have a typo?!?!

=IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Hi,

Assume the entries are within A2:A20

e.g. B2=IF(COUNTIF($A$2:A2,A2)>1,"Dup","Ok")
and copy down to B20

Regards,
A

 

[Duke Carey]

Hmmm. Don't think that works, either

Maybe:

=IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")

 

[BenjieLop]

To highlight the 3rd entry of the same team:

1. Go to "Format/Conditional Formatting"
2. For Condition 1, choose "Formula Is"
3. In the formula box, enter =countif($A$2:A2,A2)>2
4. Click on "Format"
5. Choose your highlighting color (Color box is somewhere in th
middle)
6. Click "OK"
7. Click "OK"

Hope this works for you.

Regards

 

[BenjieLop]

RD's suggested formula

*=IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")*

will work. The third time the same name is entered in Column A, the
immediate cell to its right (in Column B) will show "Dup" ...

Hmmm. Don't think that works, either

Maybe:

=IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")



----------------------------------------------------------------------------
-------------------

Hi,

Assume the entries are within A2:A20

e.g. B2=IF(COUNTIF($A$2:A2,A2)>1,"Dup","Ok")
and copy down to B20

Regards,
A

[/QUOTE]

 

[Duke Carey]

Will work...if you only want to mark the 3rd occurrence. People often want
to mark ALL entries that are dupes, to allow them to determine which to keep
and which to purge...

RD's suggested formula

*=IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")*

will work. The third time the same name is entered in Column A, the
immediate cell to its right (in Column B) will show "Dup" ...

[/QUOTE]

 

[BenjieLop]

Hi Duke,

I am not offering or commenting on a solution that *"people often want
to mark ALL entries that are dupes."* What people often want is no
what the OP was asking here.

Paul was specifically looking for a solution to identify the thir
entry as there are *"only two allowed."*


Regards.

Will work...if you only want to mark the 3rd occurrence. People ofte
want
to mark ALL entries that are dupes, to allow them to determine which t
keep
and which to purge...

RD's suggested formula

*=IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")*

will work. The third time the same name is entered in Column A, the
immediate cell to its right (in Column B) will show "Dup" ...

Hmmm. Don't think that works, either

Maybe:

=IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")

----------------------------------------------------------------------------[color=darkred[/QUOTE]

 

[RagDyer]

Appreciate you acting as my lawyer.<vbg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Hi Duke,

I am not offering or commenting on a solution that *"people often want
to mark ALL entries that are dupes."* What people often want is not
what the OP was asking here.

Paul was specifically looking for a solution to identify the third
entry as there are *"only two allowed."*


Regards.

Will work...if you only want to mark the 3rd occurrence. People often
want
to mark ALL entries that are dupes, to allow them to determine which to
keep
and which to purge...

RD's suggested formula

*=IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")*

will work. The third time the same name is entered in Column A, the
immediate cell to its right (in Column B) will show "Dup" ...

Hmmm. Don't think that works, either

Maybe:

=IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")



--------------------------------------------------------------------------

--