Duplicate challenge w/out code

[chris4ua]

I have a database of 126,000 voters in my county. I have them sorted b
street & address & precinct. I am looking to minimize the voter list t
a household list. Statistically I should be looking at 40,00
households. I have been working with an IF formul
(if(countif($I$1:I1,I1)>1,1,""). "I" is the column for the address.
use this formula twice. Once for the addresses and once for the stree
#'s. My idea is that after I run these two formulas in two seperat
columns that it will return a 1 in both columns when there is
duplicate. This works sporadically. It appears to work great and the
I start finding places where both original and duplicat
addresses/street #'s are taken out. I have also tried to use th
Advance Filter (unique records) option. It is not working for m
either. I am not versed on using Visual Basics. Can anyone help?
Thanks in advance. Here is a sample of the rows:

Id last name first name street address
63444 wells john 3286 samford avenue
63442 smith sarah 3286 samford avenue
11339 baylor eve 1422 stone ridge roa

 

[Guest]

someting like

in line 2, you would say something like

if e2=e1 AND f2=f1 then g2 = 0, otherwise g2=1

then sum up column g to get your households...I stink at if statements, so I
wrote it in "english," but it needs to be translated into Excel talk...

I have used this sort of thing before to spot duplicates, etc.

have you purified your data list so that it's always "samford avenue," and
never samford ave or anything like that...

if you had the house number and the street in the same cell, you would not
even need an if statement...

 

[chris4ua]

The problem with having the street # and the address in the same field
is that you cant truely sort the addresses. It will always divert to
the street #'s. I want a list of registered voters that live on one
street. Say I want to send out a mailing to someone who lives on a
certain street then normally I would sort by address, street and zip.
I would manually go in and try to delete out the duplicates, because
there is no point in wasting postage to the same household. Now the
list has become too overwhelming. The CountIF formula appeared to
work. But upon further review it deletes to many rows. The Advanced
Filter option also does not seem to work. This file is not to sort out
one street. This file is to give any candidate in the county a walking
list or a mailing list for households of registered voters. Hence the
need to delete out duplicated registered voter households.

 

[Guest]

then you can use the first idea I mentioned...

the IF equals AND equals...

 

[Otto Moehrbach]

Chris
There are a number of reasons why a formula or VBA would appear to be
working sporadically. One is the presence of unwanted spaces in your data.
Use of the Trim function would help with this.
What you describe indicates that VBA is the way to go. I would shudder
at the thought of working with a list of 126,000 records and not using VBA.
You say you are not versed on using VBA. VBA appears very difficult if you
don't know it. It is not that difficult in reality once you jump in.
If you wish, send me, direct, a small file with a sample of your data
that illustrates what you have to deal with. Include a few dozen records or
a few hundred, as you feel is needed. Include examples of what you want to
get at the end. I'll work with you to come up with something you can use.
My email is (e-mail address removed). Delete the "Cobia97" from this
address.
HTH Otto