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

--
Message posted from http://www.ExcelForum.com

chris4ua,
The problem is that when you go down the list your formula will find the
same address and give "1" instead of "" as expected for an example try your
formula on this data set
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 road
11339 baylor eve 1111 stone ridge road
*63444 wells john 1111 samford avenue
*63442 smith sarah 1111 samford avenue
11339 baylor eve 1422 stone ridge road

both lines marked "*" will be marked as duplicates

and as you have already fount countif formula take some time to finish its
calculations with a long lists.
workaround is to sort the list on address and street#
(put an index in another column if you want to sort back)
the sorted list will look like
Id last name first name street address
63444 wells john 1111 samford avenue
63442 smith sarah 1111 samford avenue
63444 wells john 3286 samford avenue
63442 smith sarah 3286 samford avenue
11339 baylor eve 1111 stone ridge road
11339 baylor eve 1422 stone ridge road
11339 baylor eve 1422 stone ridge road
now assuming street is in ColumnH and address is in ColumnI
use this formula in K2 and fill down to mark the duplicates
=If(And(I2=I1,H2=H1),1,"")

HTH
Cecil

"chris4ua " wrote in message
...
I have a database of 126,000 voters in my county. I have them sorted by
street & address & precinct. I am looking to minimize the voter list to
a household list. Statistically I should be looking at 40,000
households. I have been working with an IF formula
(if(countif($I$1:I1,I1)1,1,""). "I" is the column for the address. I
use this formula twice. Once for the addresses and once for the street
#'s. My idea is that after I run these two formulas in two seperate
columns that it will return a 1 in both columns when there is a
duplicate. This works sporadically. It appears to work great and then
I start finding places where both original and duplicate
addresses/street #'s are taken out. I have also tried to use the
Advance Filter (unique records) option. It is not working for me
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 road


---
Message posted from http://www.ExcelForum.com/

Having done this a time or two, do you have enough data? It would be
unusual, in most counties in the US with 126,000 registered voters, to
not have a significant fraction of the population living in apartments,
which typically share the same street address.

So how are you going to decide whether John Wells and Sarah Smith are
living in the same household, or separate apartments in the same
building?



In article ,
chris4ua wrote:

I have a database of 126,000 voters in my county. I have them sorted by
street & address & precinct. I am looking to minimize the voter list to
a household list. Statistically I should be looking at 40,000
households. I have been working with an IF formula
(if(countif($I$1:I1,I1)1,1,""). "I" is the column for the address. I
use this formula twice. Once for the addresses and once for the street
#'s. My idea is that after I run these two formulas in two seperate
columns that it will return a 1 in both columns when there is a
duplicate. This works sporadically. It appears to work great and then
I start finding places where both original and duplicate
addresses/street #'s are taken out. I have also tried to use the
Advance Filter (unique records) option. It is not working for me
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 road