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/