Compare workbooks

[Andy]

I currently get a new report every month in xls format which I have to spend
a few hours to manualy find duplicates from the last report before i can
process any new tasks required. I have tried a few tips and formulars found
online but they dont give great results.

In Short what I would like to do is open the new report compare it to the
older one which contains more information and delete any duplicate data
enteries in where coloum a is the control. Or task id. If I could do this I
would then be left with only new tasks to process and not have to waste a few
hours making sure I am not duplicating work.

Many thanks in advance.

 

[joel]

What are you calling duplicates? The ID's may be the same but the rest of
the data in the row may be different. Which coilumns have to match to be
called a duplicate.

 

[Andy]

Yes ID's would be duplicated. As the older report contains updated and more
accurate information. So I am looking for duplicate rows where coloum A is
the prime key if you like so everything in row 1 relates to the id in A1 but
only A1 may be duplicated other coloums may be but my interest lies in coloum
A on the 2 workbooks at the moment.

Many thanks

 

[joel]

I didn't test this code. Can you do a quick check and if it works it will
save me the time of testing. this code is very simple for mye and I usually
get it right the 1st time. I created a new worksheet at the end of the
workbook with the old data. The code opens the workbook with the new ID and
then closes the book so it won't change this data. I didn't name the new
worksheet it will just be called sheetX where X is one higher than the sheet
name in the workbook.

 

[Andy]

Sounds great Joel but where is the code ????

I didn't test this code. Can you do a quick check and if it works it will
save me the time of testing. this code is very simple for mye and I usually
get it right the 1st time. I created a new worksheet at the end of the
workbook with the old data. The code opens the workbook with the new ID and
then closes the book so it won't change this data. I didn't name the new
worksheet it will just be called sheetX where X is one higher than the sheet
name in the workbook.

 

[joel]

I tested the code and it worksed 1st time. I assumed the old IDs are in the
workbook with the macro called "Sheet1" and the workbook with the new IDs are
in the 1st sheet of the workbook.

Sub GetNewIds()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls", _
Title:="Open New Workbook")
If filetoopen = False Then
MsgBox ("Cannot open file - Exitig Macro")
Exit Sub
End If

'sheet where old IDs are located
Set OldSht = ThisWorkbook.Sheets("Sheet1")

Set NewBk = Workbooks.Open(Filename:=filetoopen)
Set NewSht = NewBk.Sheets(1)


With ThisWorkbook
'Create new sheet
Set NewIDSht = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
With NewIDSht
'copy Header Row
NewSht.Rows(1).Copy _
Destination:=.Rows(1)
NewIDRow = 2
NewShtRow = 2
Do While NewSht.Range("A" & NewShtRow) <> ""
ID = NewSht.Range("A" & NewShtRow)
Set c = OldSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
NewSht.Rows(NewShtRow).Copy _
Destination:=.Rows(NewIDRow)

NewIDRow = NewIDRow + 1
End If
NewShtRow = NewShtRow + 1
Loop
End With
End With

End Sub

 

[Andy]

Thats great Joel works fine. Could I be a right royal pain and ask for just
one thing more. Is it possible for it to give me a count of matching records
just for quick reference if not now worries. Appreciate all you have done its
gonna save me hours.

 

[joel]

the number of matching items is the Last row where data is put minus 1 for
the header row. the variable NewIDRow is that value except it is pointing to
the row where the next row of data is put. therefore, if you take NewIDRow
and subtract 2 you get the total number of matches.

Numberof Matches = NewIDRow - 2

 

[Andy]

Joel
I am sorry but not sure what I am doing wrong here. Tried again with your
origional code, which opens the new report fine, it then creates a new
worksheet at the end of the older report. Then it seams to stop. When I first
tried it I just took it that there were no matches, I have now put some extra
numbers in coloum A but these dont show up in the new work sheet. Which is
why I asked for the quick look up of mathing records, which I am sorry but
couldnt get to work.

Sorry for miss leading you again and thanks for your help to date.

 

[joel]

I'm confused because I'm not sure which workbook you are refereing to.

1) the code will alway create a new sheet at the end of the workbook where
the code is located. Make sure you are looking at the last sheet

2) The code starts on Row 2 of the workbook it opens and stops when it finds
a blank cell in column A. if you have bnak cells in column A I have to make
a minor change to the code

3) You said you put extra data in column A. I hope you meant the workbook
that gets opened and not Colun A in the wsorkbook where the macro is located.

4) the code only looks at Sheet1 in the workbook with the macro and only the
1st tab in the workbooks that are opened.

5) I made 1 change to the code below to close the workbook at the end. I'm
thinking that maybe you made changes to the workbook and forgot to save the
changes. If yo started with the workbook open and didn't save the changes
the code will reopen the workbook and you will loose you new changes.

Sub GetNewIds()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls", _
Title:="Open New Workbook")
If filetoopen = False Then
MsgBox ("Cannot open file - Exitig Macro")
Exit Sub
End If

'sheet where old IDs are located
Set OldSht = ThisWorkbook.Sheets("Sheet1")

Set NewBk = Workbooks.Open(Filename:=filetoopen)
Set NewSht = NewBk.Sheets(1)


With ThisWorkbook
'Create new sheet
Set NewIDSht = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
With NewIDSht
'copy Header Row
NewSht.Rows(1).Copy _
Destination:=.Rows(1)
NewIDRow = 2
NewShtRow = 2
Do While NewSht.Range("A" & NewShtRow) <> ""
ID = NewSht.Range("A" & NewShtRow)
Set c = OldSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
NewSht.Rows(NewShtRow).Copy _
Destination:=.Rows(NewIDRow)

NewIDRow = NewIDRow + 1
End If
NewShtRow = NewShtRow + 1
Loop
End With
End With

NewBk.close savechanges:=False

End Sub

 

[Andy]

That works perfectly. Apologise for first mistake the additional data was in
coloum A of the old report and not the new. Just 1 last thing can you get it
to give you a count of matching data if so this would be great.

 

[joel]

What I did in my last posting is what we call at work "Fish Boning"a problem
which is basically listing every reason for the problem. It seemed to work.

I answered you last question proeviously (see below). I'm not sure where
you want the total to be displayed. On the worksheet somewhere or in a
message box.

the number of matching items is the Last row where data is put minus 1 for
the header row. the variable NewIDRow is that value except it is pointing to
the row where the next row of data is put. therefore, if you take NewIDRow
and subtract 2 you get the total number of matches.

Numberof Matches = NewIDRow - 2