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.

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" wrote:

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.

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" wrote:

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" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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.

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

"joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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" wrote:

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

"joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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.

"Andy" wrote:

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

"joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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.