# Search for Duplicate Names within Two Separate Sheets

Options

Good Morning,

I am trying to enter a formula that will give a show me when a duplicate name is enter into one or the other sheet I am working on. We will be entering names daily into each sheet and we need to quick way to find if we have entered the same name in twice. Please let me know what formula you think will be best. I keep getting the errors unparseable and circular reference when I try to enter in the formulas.

Thank you,

Caroline Bush

• ✭✭✭✭✭✭
Options

Do you mean if the name is already on sheet 1 then you want it to flag on sheet 2, or if it is entered either on sheet one or multiple times in sheet 2?

• Options

Hey Paul,

The name will be entered on sheet 1. If that person declines our service, their name will fall to sheet 2. I need a way to see if the name is already listed on either sheet 1 or sheet 2, so that way we do not reach out to them again.

Thank you,

Caroline

• ✭✭✭✭✭✭
Options

Ok. So duplicates across both sheets would look something like this...

=IF(OR(COUNTIFS({Other Sheet Name Column}, [Name Column]@row) > 0, COUNTIFS([Name Column]:Name Column]:[Name Column]@row) > 1), 1)

Basically it says that if it is found on the other sheet even once or if it is found more than once on the current sheet (to account for the current row), flag.

• Options

Hey,

Here is how I make the formula look and it is still giving me the error unparseable.

=IF(OR(COUNTIFS({{Quarantine and Isolation Final Range 1}}, [{{Quarantine and Isolation Final Range 1}}]@row) > 0, COUNTIFS([Student Name]:[Student Name]:[Student Name]@row) > 1), 1)

• ✭✭✭✭✭✭
Options

You have too many brackets added in and the syntax is just a little off. Try this...

=IF(OR(COUNTIFS({Quarantine and Isolation Final Range 1}, [Student Name]@row) > 0, COUNTIFS([Student Name]:[Student Name], [Student Name]@row) > 1), 1)

• Options

I am now getting the circular reference error.

• ✭✭✭✭✭✭
Options

Which column are you putting the formula in?

• Options

I am putting in the bottom of the sheet in the Student Name column. I moved it to the notes column, and got the same result.

• ✭✭✭✭✭✭
Options

What column(s) are you referencing in the {Quarantine and Isolation Final Range 1} range?

• Options

The Student Name column.

• Options

I think I have now figuered it out! it is giving me a blank square. If there was to be a duplicate, would there be a number in that box?

• ✭✭✭✭✭✭
Options

That is correct. I wrote the formula to be placed in either a checkbox, flag, or star type of column. In a text/number column duplicates will be marked with a "1" and non-duplicated entries will remain blank.

• Options

Wahoo! It is working now. Thank you so very much for your help!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

Hey Paul,

How would you add ISBLANK to the mix? I reused this formula and I need it to ignore cells that are blank.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!