Search for Duplicate Names within Two Separate Sheets
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
Answers
-
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?
-
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
-
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.
-
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)
-
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)
-
I am now getting the circular reference error.
-
Which column are you putting the formula in?
-
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.
-
What column(s) are you referencing in the {Quarantine and Isolation Final Range 1} range?
-
The Student Name column.
-
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?
-
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.
-
Wahoo! It is working now. Thank you so very much for your help!!
-
Happy to help. 👍️
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!