Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro?
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). "minimoi" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |