how can I count duplicate entries in two ranges?
 

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

how can I count duplicate entries in two ranges?
 

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

how can I count duplicate entries in two ranges?
 

Go to this link, Mr. Pearson has many examples on how to deal with
duplicates.

http://www.cpearson.com/excel/topic.htm
HTH
"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

how can I count duplicate entries in two ranges?
 

Will this work on a range such as (B2:C1001)?
Will accept any range.

Q followed by an 8 digit number ... a problem?
No

Finally, I've never installed a function. The M$ help doesn't help me
atall, is there a short and sweet description you could afford me?

Copy code from post.
From Excel UI, open target workbook.
Press:
Alt+F11, 'opens macro editor
Ctrl+R 'opens Project Explorer
Alt+I, 'opens Insert Menu.
M. 'chooses Module
Paste code.
Save workbook.
Try it.

A formula using this function may take a while to recalc on a large range
since it goes thru each cell individually.
--
Bob Kilmer


"Father Guido" wrote in message
...
On Thu, 25 Sep 2003 21:11:42 -0400, "Bob Kilmer"
wrote:

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.

Hi,

Thanks for the info. Will this work on a range such as (B2:C1001)?
My data will always start with the letter Q followed by an 8 digit
number. Will this be a problem?
Finally, I've never installed a function. The M$ help doesn't help me
atall, is there a short and sweet description you could afford me?

Thanks very much!!!

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good