data validation

[WillemS]

Hi there, I need some help, please. I receive various email addresses in excel, in the same workbook in a sheet called Mail by manual input from various users, which I then use the specific email address received to send a letter via outlook to the customer linked to the email address entered by the requesting user. To prevent the duplication of using the same email address again if a user makes a mistake to load the same email address in the excel sheet called Mail, a few days later and or at any given point, when the email address was already used by me to send a letter to the client, I need some sort of database and or VBA code and or process to validate the new email address as it arrives in the excel sheet called Mail, input by various users, to automatically see whether it was already used to send a mail to a customer or not. If the email address was already used it must reject it and move it to a rejection list in the same workbook on a sheet called Reject and if not used before it must move it to an awaiting email sheet in the same workbook called Await. Please help.Much appreciated.Thank you .W

 

[Crystalyzer]

Posting a copy of the workbook you receive would be helpful.

 

[WillemS]

My apology I am new to the site. How do I upload my file?

 

[Crystalyzer]

Check this out

Guidelines

Guidelines for MrExcel Message Board Use

www.mrexcel.com

 

[WillemS]

Thanks will do so

 

[Crystalyzer]

I re-read your request and I think you can start to accomplish what you want at the user input level to avoid duplicates by adding a column with a formula that will check for duplicates. From there you can then filter the list and copy past information into the sheets you want for Reject or Await.

Book1
	A	B
1	Email Addresss	New (Y/N)
2	Greg@gmail.com	Y
3	Greg@Yahoo.com	Y
4	John@aol.com	Y
5	Harry@harrysconstruction.org	Y
6	Greg@gmail.com	N
7	Sam@yourdomainhere.net	Y
8	Harry@harrysconstruction.org	N
Email

Cell Formulas
Range	Formula
B2:B8	B2	=IF(ISERROR(MATCH(A2,$A$1:A1,0)),"Y","N")