Stop Duplicated rows

**toqi786** · 10-28-2009, 10:49 AM

Hi, im creating a Customer Details Spreadsheet. I'm Using a UserForm to Input the Customer Details. For the Street Name and Area I'm using a Combo Box. For the House Number i have a seperate column.

What I wish to do is input data using the UserForm, but i don't want to duplicate the same house number in that street

Example:

I can add 123 Springfield Road and also add 123 Johnson Road [more that one with same house number] and I can also add 109 Johnson Road [more than one per street], but i dont want to be able to add 123 Springfield Road again, and when i click on submit on the UserForm it gives me a Error.

Thankx

**JBeaucaire** · 10-28-2009, 01:04 PM

1) Add an "error check" column that is basically a concatenation of your address fields.... =A1 & " " & B1
Anytime an item is added to your database, make sure that cells gets filled in, too. Let's say that column is C.

2) Do an IF test at the start of your SUBMIT routine. Something like:

Please Login or Register  to view this content.

**Palmetto** · 10-28-2009, 01:55 PM

Another option: Use the Find method to see if the Street Name exists and whether or not the house number is different.

Basic Code:

Please Login or Register  to view this content.

**JBeaucaire** · 10-28-2009, 02:13 PM

That won't find all the matches. If the address to search for is 123 Main St and the first 123 it finds is 123 Lemon Lane, it would mark that as not a match, when in fact 123 Main St may exist elsewhere.