Avoid duplicate entries across 2 worksheets

In an Excel workbook, I've got data in one column on two worksheets. The second worksheet contains old data. New data is entered in the first worksheet. I need to avoid duplicate entries, i.e. if I try to enter a phone number that is already on either sheet, it will not allow the phone number to be added.

I have no problem accomplishing this with a COUNTIF formula in data validation, but I can't seem to figure out how to check to see if it's on the second worksheet.

New numbers are constantly being added and it can't be something where you have to go back and run a search afterwards, it needs to notify the user that the number exists right when it is entered.

Any help is greatly appreciated!

-Chris

another method would be to copy list one beneath list two (so you have one long list)...then just do a pivot table on the large list (put phone # in the row field) ... this will combine like data....then just copy and paste your new list

(sorry my example was created in newer version so pivot might not work)

Originally Posted by Marcol

Try this

Make make your list on Sheet2 a named range, say "OldList"

Then in the range you want to validate, say column A
Data Validation > Custom

=COUNTIF(OldList,A1)=0

Hope this helps

Hi Marcol,

Thanks, I tried and it didn't seem to work. Do I need to reference the second worksheet in the formula?

TechRetard, I need to be able to verify that the input is unique without taking extra steps. Once the number is put in, it needs to kick back with an error message if the number exists on either worksheet.

Thanks guys!

Marcol,

Thanks for posting that! How would I combine the two example spreadsheets so that it will only allow the new data if it doesn't exist on sheet1 OR on sheet2?

I'm having trouble figuring out how those worksheets work I guess. It doesn't stop me from entering duplicate values from what I can see.

I've attached the file I'm working with, maybe it will be easier to see what specifically I'm trying to do.

In the tab/sheet titled "New Leads", I'll be entering data including email and phone number. Those two columns are the ones that need to be unique.

After the new leads are entered, they will be processed one by one with a phone call and/or an email. When they are processed, they will be moved to either the "No Response" sheet, the "Not Interested" sheet, or the "Interested" sheet, depending on the outcome.

Rather than checking those 3 sheets for duplicates being entered on "New Leads", I created the "Master List" sheet where a copy of ALL the leads will go as they are taken from the "New Leads" sheet and categorized in one of the 3 categories I mentioned before. This way (I figure), if I try to put a phone number in to "New Leads" that already exists somewhere else (either No Response, Not Interested, or Interested), it will only need to check the "Master List" to see if the phone number or email has already been processed.

Thanks again for all the help!

Hmm?

Not what I expected to see, I'll think it over.
Got to go now, I'll give it a go tomorrow if nobody else solves your problem before then.

Cheers.

Thank you very much for all your help, I'm still trying to figure it out on my own as well....
Surprised at how difficult this is turning out to be! oh well :-)

Originally Posted by Marcol

Hmm?

Not what I expected to see, I'll think it over.
Got to go now, I'll give it a go tomorrow if nobody else solves your problem before then.

Cheers.

Dear Marcol;

I was trying to apply the guidelines what you have given through above posts but it doesn't work properly. Could you advice me in this regard?

I have attached the excel file here.

Thanks in advance.