I think I need to start from the beginning.
I get information from SAP, save into an exel file, some of this information
changes weekly but not all. I want to have the previous weeks list open in
exel, then the new one (I think I could put in sheet 2?) then have a macro
compare all information, if any duplicates that are present, I would like to
delete, eg the only duplicates would arise from the weekly change, so there
would only ever be two rows the same, as I have already analysed the original
data I would like the copy deleted, leaving the original row in tact.

The worksheets are always eight colmns, but rows differetiate on a weekly
basis.
The information identifer for possible duplication comes from column F

If you can help could you please put the info in a cut & pate format.

Huge thanks so far Joel

"Joel" wrote:

Modify these 3 lines as required

Set sht1 = Workbooks("Book1.xls").Sheets("Sheet1")
Set sht2 = Workbooks("Book2.xls").Sheets("Sheet1")

SortCol = "A"
----------------------------------------------------------------------------------------------
Sub combine()

Set sht1 = Workbooks("Book1.xls").Sheets("Sheet1")
Set sht2 = Workbooks("Book2.xls").Sheets("Sheet1")

SortCol = "A"

'Create New sheet a copy of sheet 1
With ThisWorkbook
sht1.Copy after:=.Sheets(.Sheets.Count)
Set newsht = ActiveSheet
End With

With newsht
LastRow = .Range(SortCol & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

With sht2
LastRow = .Range(SortCol & Rows.Count).End(xlUp).Row
Set CopyRange = .Rows("1:" & LastRow)
CopyRange.Copy Destination:=newsht.Rows(NewRow)
End With

'Sort New Sht
LastRow = .Range(SortCol & Rows.Count).End(xlUp).Row
Set SortRange = .Rows("1:" & LastRow)
SortRange.Sort _
key1:=.Range(SortCol & "1"), _
order1:=xlAscending, _
Header:=xlNo

'Delete duplicate rows
RowCount = 1
Do While .Range(SortCol & (RowCount + 1)) < ""
If .Range(SortCol & RowCount) = _
.Range(SortCol & (RowCount + 1)) Then

.Row(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End With

End Sub


"minimoi" wrote:

The easiest method is to copy both worksheets into a new worksheet and then
sort by the column with the identifier. Then compare each row against the
next row to see if the identifier is the same. Then delete one of the
duplicate rows.

The above is what I want to do but how do I automate it?

"Joel" wrote:

Givbe more details. You say you are working with A worksheet (one) and then
say you want to open BOTH spreadsheets. Let us know if there are more than
one workbook and the sheet names.

The easiest method is to copy both worksheets into a new worksheet and then
sort by the column with the identifier. Then compare each row against the
next row to see if the identifier is the same. Then delete one of the
duplicate rows.

"minimoi" wrote:

Hi,
I am working with a spreadsheet that contains information that is updated
weekly, some of the information is duplicated, there are eight colums with
only one that defines the duplication.

How can I open both spreadsheets at the same time and merge them? then
delete all duplications?

Any help would be greatly appreciated.