Checking for duplication across two fields

Options

Hi Community

Have used the formula posted to this forum to identify duplicates in a single column using [IF(COUNTIF())} formula. I have a site/dept type issue split across two columns and need to identify when the same site/dept combination has been used in another record. I have tried using AND in the COUNTIF() but just can't get it to work. Should that logic work and it is just me typing incorrectly? Anyone got a different neat solution?

Tags:

Best Answer

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @RayB I would try using a helper column where you would add the site and department. [SITE]@Row + [DEPARTMENT]@Row

    This way you can treat each record as unique and not have to do the "math"

    This way you don't have to use the AND statement. you can also correct for stray characters like extra spaces etc..

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @RayB I would try using a helper column where you would add the site and department. [SITE]@Row + [DEPARTMENT]@Row

    This way you can treat each record as unique and not have to do the "math"

    This way you don't have to use the AND statement. you can also correct for stray characters like extra spaces etc..

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • RayB
    RayB ✭✭
    Options

    Thanks Brent - never thought of doing that and such a simple solution - that's why I like this community so much :-)

    .....would still be very interested to understand if a formula could be written to do the same and what the syntax would look like...just as an intellectual challenge