macro?

[minimoi]

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.

 

[Joel]

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]

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]

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]

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]

I not sure what you mean by "Cut and Paste". I don';t know enough about you
SAP utility to help get the information from the utility. So it is probably
best to start from two sheets in the same workbook.. You can change the
first three lines as shown below to sort on sheet 1 & 2 using column f.

Set sht1 = Thisworkbook.Sheets("Sheet1")
Set sht2 = Thisworkbook.Sheets("Sheet2")

SortCol = "F"

I think it is better to create a new sheet so you don't destroy the old
information. After the macro is run you can easily delete the sheets you
don't want (the previous week and the download SAP page).