+ Reply to Thread
Results 1 to 90 of 90

Simple Formula?

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Simple Formula?

    Hi,

    I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an 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 many arsenal entries as only two are allowed

    Can anyone help?

    Regards

    Paul

  2. #2
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  3. #3
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  4. #4
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    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 the middle)
    6. Click "OK"
    7. Click "OK"

    Hope this works for you.

    Regards.
    BenjieLop
    Houston, TX

  5. #5
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  6. #6
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    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 Originally Posted by Duke Carey
    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >

  7. #7
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  8. #8
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    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.



    [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...

    "BenjieLop" wrote:
    [color=blue]
    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > Believe you have a typo?!?!
    > > >
    > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > >
    > > > --
    > > >
    > > > Regards,
    > > >
    > > > RD
    > > >

    > > ----------------------------------------------------------------------------[color=darkred]

  9. #9
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  10. #10
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  11. #11
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  12. #12
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  13. #13
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  14. #14
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  15. #15
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  16. #16
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  17. #17
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  18. #18
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  19. #19
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  20. #20
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  21. #21
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  22. #22
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  23. #23
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  24. #24
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  25. #25
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  26. #26
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  27. #27
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  28. #28
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  29. #29
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  30. #30
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  31. #31
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  32. #32
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  33. #33
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  34. #34
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  35. #35
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  36. #36
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  37. #37
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  38. #38
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  39. #39
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  40. #40
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  41. #41
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  42. #42
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  43. #43
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  44. #44
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  45. #45
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  46. #46
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  47. #47
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  48. #48
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  49. #49
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  50. #50
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  51. #51
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  52. #52
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  53. #53
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  54. #54
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  55. #55
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  56. #56
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  57. #57
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  58. #58
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  59. #59
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  60. #60
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  61. #61
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  62. #62
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  63. #63
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  64. #64
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  65. #65
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  66. #66
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  67. #67
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  68. #68
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  69. #69
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  70. #70
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  71. #71
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  72. #72
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  73. #73
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  74. #74
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  75. #75
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  76. #76
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  77. #77
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  78. #78
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  79. #79
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  80. #80
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  81. #81
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  82. #82
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  83. #83
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  84. #84
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  85. #85
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  86. #86
    RagDyer
    Guest

    Re: Simple Formula?

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

    RD

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

    "BenjieLop" <[email protected]> wrote
    in message news:[email protected]...[color=blue]
    >
    > 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.
    >
    >
    >
    > Duke Carey Wrote:[color=green]
    > > 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...
    > >
    > > "BenjieLop" wrote:
    > >
    > > >
    > > > 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" ...
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Duke Carey Wrote:
    > > > > Hmmm. Don't think that works, either
    > > > >
    > > > > Maybe:
    > > > >
    > > > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > > > >
    > > > >
    > > > > "RagDyeR" wrote:
    > > > >
    > > > > > Believe you have a typo?!?!
    > > > > >
    > > > > > =IF(COUNTIF($A$2:A2,A2)>2,"Dup","Ok")
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Regards,
    > > > > >
    > > > > > RD
    > > > > >
    > > > >

    >
    > --------------------------------------------------------------------------

    --[color=darkred]
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:

    http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >



  87. #87
    Duke Carey
    Guest

    Re: Simple Formula?

    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...

    "BenjieLop" wrote:

    >
    > 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" ...
    >
    >
    >
    >
    >
    > Duke Carey Wrote:
    > > Hmmm. Don't think that works, either
    > >
    > > Maybe:
    > >
    > > =IF(COUNTIF($A$20:A2,A2)>2,"Dup","Ok")
    > >
    > >
    > > "RagDyeR" wrote:
    > >
    > > > 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

    > > !
    > > >

    > > ----------------------------------------------------------------------------
    > > > -------------------
    > > >
    > > > "aristotle" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > 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
    > > >
    > > > "pauldaddyadams" wrote:
    > > >
    > > > >
    > > > > Hi,
    > > > >
    > > > > I have a problem which I need someone to look at. I have a list of

    > > 11
    > > > > teams and I need a formula to check the data and for it to identify

    > > an
    > > > > 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 many
    > > > > arsenal entries as only two are allowed
    > > > >
    > > > > Can anyone help?
    > > > >
    > > > > Regards
    > > > >
    > > > > Paul
    > > > >
    > > > >
    > > > > --
    > > > > pauldaddyadams
    > > > >

    > > ------------------------------------------------------------------------
    > > > > pauldaddyadams's Profile:
    > > > http://www.excelforum.com/member.php...o&userid=22499
    > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=394615
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


  88. #88
    Duke Carey
    Guest

    Re: Simple Formula?

    Hmmm. Don't think that works, either

    Maybe:

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


    "RagDyeR" wrote:

    > 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 !
    > ----------------------------------------------------------------------------
    > -------------------
    >
    > "aristotle" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    > "pauldaddyadams" wrote:
    >
    > >
    > > Hi,
    > >
    > > I have a problem which I need someone to look at. I have a list of 11
    > > teams and I need a formula to check the data and for it to identify an
    > > 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 many
    > > arsenal entries as only two are allowed
    > >
    > > Can anyone help?
    > >
    > > Regards
    > >
    > > Paul
    > >
    > >
    > > --
    > > pauldaddyadams
    > > ------------------------------------------------------------------------
    > > pauldaddyadams's Profile:

    > http://www.excelforum.com/member.php...o&userid=22499
    > > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    > >
    > >

    >
    >
    >


  89. #89
    RagDyeR
    Guest

    Re: Simple Formula?

    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 !
    ----------------------------------------------------------------------------
    -------------------

    "aristotle" <[email protected]> wrote in message
    news:[email protected]...
    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile:

    http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >




  90. #90
    aristotle
    Guest

    RE: Simple Formula?

    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

    "pauldaddyadams" wrote:

    >
    > Hi,
    >
    > I have a problem which I need someone to look at. I have a list of 11
    > teams and I need a formula to check the data and for it to identify an
    > 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 many
    > arsenal entries as only two are allowed
    >
    > Can anyone help?
    >
    > Regards
    >
    > Paul
    >
    >
    > --
    > pauldaddyadams
    > ------------------------------------------------------------------------
    > pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
    > View this thread: http://www.excelforum.com/showthread...hreadid=394615
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1