Identifying new entries in separate workbooks

[Lee Jeffery]

Using Excel 97 on WINNT.

Hi,

After many frustrating attempts and loads of Googling and
experimentation, as well as trying out previous advice given on this
forum, I still cannot gain the result I need when comparing the
contents of workbooks I receive on a fortnightly basis. It may be that
I haven't explained myself clearly enough. I have tried Chip Pearson's
examples for deleting duplicate rows, but this doesn't give me the
results I need. Dave Peterson also supplied some code a while back but
I can't get anywhere with this (probably me rather than the code I
strongly suspect!).

I have tried to compare workbooks ( ). I have also tried copying
the range of data from the new report to the bottom of the old report,
adding a helper column and then identifying and deleting duplicate
entries based on the helper column but this isn't giving me a list of
exclusively new entries which is really what I want.

I receive a report each fortnight (currently containing about 5000
rows) listing information about customer debts. A customer number is
unique to the customer and may appear more than once on a report but
always in Column B. The debt codes identify the type of debt and may
appear many times on a report for many different customers but always
in Column D.

While a debt is outstanding, the customer will continue to appear on
these fortnightly reports so the information may not be new and
deleting a duplicate row still leaves me with an existing entry. A
customer can incur a new debt which means that they will not only
appear on the new report with their previously existing debt, but also
with their new debt showing a new code. An example would be:

Old Report:
Customer Number Debt Code
12345678 91R1

New Report:
Customer Number Debt Code
12345678 91R1
12345678 91R2

In this example, I want the existing customer information deleted
because I already know about it. I want the new information (row
showing 91R2) retained so an investigation can be performed.
Alternatively, new data can be copied to a new worksheet without having
to delete existing information.

I already have code to open each workbook and to save the amended
workbook under a new name so the actual original workbooks won't be
changed as these are used for other purposes.

Please, please, please help. I have spent many frustrating hours and am
very inexperienced at writing VBA code so it is probably that I am
applying knowledge incorrectly. If I can get this right, it will save
others (and me) many hours of manual checking to find new information
and will eliminate manual errors.

It seems this should be a straightforward exercise but straightforward
is above my ability level.

Many, many TIA.

Lee Jeffery.

 

[Frank Stone]

hi
if i understand you correctly, you wish to know all of
your customers debt codes. if a customer has more than 1
debt code, you wish to see all. that that is so then you
will need to copy and paste the new fortnight report at
the bottom of the running report. then run this code.
the code will sort but customer then debt code then go
down the list deleting double debt codes for each customer.

Sub DelDubs()

Dim c1 As Range
Dim c2 As Range
Dim d1 As Range
Dim d2 As Range

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Set c1 = Range("A2")
Set d1 = Range("D2")
Do While Not IsEmpty(c1)
Set c2 = c1.Offset(1, 0)
Set d2 = d1.Offset(1, 0)
If c1.Value = c2.Value _
And d1.Value = d2.Value Then
d2.EntireRow.Delete
Set c2 = c1.Offset(1, 0)
Set d2 = d1.Offset(1, 0)
Else
Set c1 = c2
Set d1 = d2
End If
'c1.select 'optional. you can watch it zip down the
sheet.
Loop
End Sub

 

[Frank Stone]

hi again,
i tested the code. it works. if i understood you correctly