Search for Duplicate Names within Two Separate Sheets

Caroline Bush

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

Paul Newcome

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?

Caroline Bush

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

Paul Newcome

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.

Caroline Bush

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)

Paul Newcome

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)

Caroline Bush

I am now getting the circular reference error.

Paul Newcome

Which column are you putting the formula in?

Caroline Bush

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.

Paul Newcome

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

Caroline Bush

The Student Name column.

Caroline Bush

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?

Paul Newcome

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.

Caroline Bush

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

Paul Newcome

Happy to help. 👍️

Katlyn Gossett

Hey Paul,

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