something like this user defined function perhaps?

Public Function dupes(rng As Range) As Long
Dim col As New Collection
Dim cell As Range
Dim count As Long
count = 0
On Error Resume Next
For Each cell In rng.Cells
col.Add cell.Text, cell.Text
If Err.Number < 0 Then
count = count + 1
Resume 0
End If
Next
dupes = count
Set col = Nothing
End Function


In a worksheet use like this: =dupes(B1:B9)
Will accept any range.

--
Bob Kilmer


"Norm" wrote in message
om...
Hi,

Using Excel2002 with Win2000Pro.

I have a problem determining records added and removed on a daily
basis. I have x columns with more columns being added daily. What I
need t do to determine the number of new records today (incoming) and
how many records from yesterday went away (outgoing). If I could have
a formula that could tell me how many duplicate records exist in these
two columns, I could subtract that number from todays total to find
the number of incoming records added, similarly, subtracting the
duplicates from yesterdays total would give me number of outgoing
records removed. Each record will only appear once in each range.

The two columns/ranges I need to check for duplicates will change
daily. Todays column will become tomorrow's yesterday column etc. I'd
prefer not adding an extra column and using lookups to test for each
record individually, see my previous post regarding my attempts to use
an extra column.

All suggestions welcome.

Thanks!!!

Norm