Formula to identify the first duplicate record

Formula to look at "Client Name" and if duplicate only return Yes for the first one, if not duplicate then just return Yes but I'm getting an error. Help?

=IF(COUNTIF([Client Name]$1:[Client Name]@row, [Client Name]@row) = 1, "Yes", IF(MIN(COLLECT(ROW([Client Name]$1:[Client Name]@row), [Client Name]$1:[Client Name]@row, @cell = [Client Name]@row)) = ROW([Client Name]@row), "Yes", ""))

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 01/29/24

    Not sure what your sheet looks like. And can't provide a complete formula without seeing it. But:

    1. Column references should be something like [Client Name]:[Client Name]
    2. Countif should looks like
    =COUNTIF([Client Name]:[Client Name],[Client Name]@row = 1
    


    ...

  • Connie Cochran
    Connie Cochran ✭✭✭✭

    I had used AI to write it at first because I was stumped and that is what it returned. When I removed the $1 sign (it looked weird to me too) I am still getting an unparsable error

    What I am looking for is just something to flag every unique name in the Client Name column

    =IF(COUNTIF([Client Name]:[Client Name]@row, [Client Name]@row) = 1, "Yes", IF(MIN(COLLECT(ROW([Client Name]:[Client Name]@row), [Client Name]:[Client Name]@row, @cell = [Client Name]@row)) = ROW([Client Name]@row), "Yes", ""))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!