How do I write a formula that only finds unique or duplicate text from a single column?

Options

I have a sheet with 7,195 rows of data. I'm trying to write a formula that looks in the "Full Name" column to find which names are unique and which are duplicates. Any suggestions?

I've tried this formula:

=IF(Countif([Full Name]1:[Full Name]7195, 1)

I also thought using the DISTINCT function may help, but I'm not sure.

Tags:

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 08/20/20
    Options

    Hi @Rebekah Brock.

    According to what I understand, I wouldn't use Distinct in this case, as distinct will gather every full names once. So doesn't look like a good choice.

    I would rather go with some helper column here. Flag type symbol column, with this formula:

    =IF(COUNTIFS([Full Name]:[Full Name], [Full Name]@row)>1, 1, 0)

    Here we'll only separate names that appears once from those that appears more than once.

    If Names appears more than once, this will raise a flag.

    Thus, on top of that, I would suggest to add some conditional formatting based on this helper column and change the color of the full row/[Full Name] column if name is a duplicate and another one when names are unique.

  • Rebekah Brock
    Rebekah Brock ✭✭
    edited 08/20/20
    Options

    @David Joyeuse Hi David,

    Thank you for your comment and your advice!

    Your formula worked! Thank you so much!

    Do you know if there's a way for me to write "duplicate" and "unique" into the formula instead of only have 0s and 1s populate?

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    @Rebekah Brock

    For sure.

    I went with a flag type cell because that's how I usually do it.

    You'll have to change the column to dropdown list with either "duplicate" or "unique" within it.

    =IF(COUNTIFS([Full Name]:[Full Name], [Full Name]@row)>1, "duplicate", "unique")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!