how can I count duplicate entries in two ranges?

  • Thread starter Thread starter Norm
  • Start date Start date
N

Norm

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
 
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.
 
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.
Click to expand...

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
 
Go to this link, Mr. Pearson has many examples on how to deal with
duplicates.

http://www.cpearson.com/excel/topic.htm
HTH
Click to expand...
Been there, done that. I'm working on one method right now. I'll let
everyone know how it works out. The hard part is making it flexible so
that it can change compared ranges each day. Thanks.

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good
 
Will this work on a range such as (B2:C1001)?
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?
Click to expand...

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.
 
Thanks Bob,

I guess I had it installed right, but it doesn't seem to come up with
the answer I expect when one col is longer than the other (includes
extra blanks at the end to balance the range selection out against the
data in the second col.

Anyway I figured out how to keep the rows constant and leave the cols
relative. I'm ashames to say I forgot about the four states of absolute
referencing. I was either all absolute, or all relative, whereas I
settled on half and half. C$R:C$R allows me to fill the formula down and
leave the formula available for other cols properly. I then put a one if
the adjacent cell can be looked up (Duped) in the col to the left of
that one. I then do a quick add up and freeze the answers then delete
the data in the helper col. <sigh>

Thanks again though, I did learn something about functions I didn't know
before.

Norm

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.
Click to expand...


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