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

Rebekah Brock

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.

David Joyeuse

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

@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

@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")