| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help w/Duplicates & Macros
anyway to delete the duplicate files (File #) but leaving the open date &
open time of the original (first opened file) + close date & close time from the last opened file ..?? ...including a total sum per all files for CH & Issuer?? File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 1 1 04/01/08 07:30 A100444 04/02/08 05:00 3 0 04/03/08 13:15 A100445 04/01/08 08:00 1 0 04/01/08 08:30 A100445 04/02/08 08:30 1 1 04/03/08 16:00 A100445 04/03/08 08:45 1 1 04/05/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 Result will look like this... File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 4 2 04/01/08 13:15 A100445 04/01/08 08:00 3 2 04/01/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 I got helped last time and managed to use a macro to delete duplicates with open date and time but now I need the sum of all files (including duplicates) for CH and Issuer + the close date and time of last filed open ..make sense .... any ideas will be greatly appreciate it !! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help w/Duplicates & Macros
Mayte,
maybe something like this... Sub test() Dim lRow As Long Dim lLastRow As Long With ActiveSheet lLastRow = .Range("A1").End(xlDown).Row For lRow = lLastRow To 2 Step -1 If .Range("A" & lRow).Value = .Range("A" & lRow - 1).Value Then With ActiveSheet .Range("D" & lRow - 1).Value = .Range("D" & lRow - 1).Value + _ .Range("D" & lRow).Value .Range("E" & lRow - 1).Value = .Range("E" & lRow - 1).Value + _ .Range("E" & lRow).Value .Range("F" & lRow - 1).Value = .Range("F" & lRow).Value .Range("G" & lRow - 1).Value = .Range("G" & lRow).Value .Rows(lRow).EntireRow.Delete End With End If Next lRow End With End Sub -- Hope that helps. Vergel Adriano "Mayte" wrote: anyway to delete the duplicate files (File #) but leaving the open date & open time of the original (first opened file) + close date & close time from the last opened file ..?? ...including a total sum per all files for CH & Issuer?? File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 1 1 04/01/08 07:30 A100444 04/02/08 05:00 3 0 04/03/08 13:15 A100445 04/01/08 08:00 1 0 04/01/08 08:30 A100445 04/02/08 08:30 1 1 04/03/08 16:00 A100445 04/03/08 08:45 1 1 04/05/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 Result will look like this... File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 4 2 04/01/08 13:15 A100445 04/01/08 08:00 3 2 04/01/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 I got helped last time and managed to use a macro to delete duplicates with open date and time but now I need the sum of all files (including duplicates) for CH and Issuer + the close date and time of last filed open ..make sense ... any ideas will be greatly appreciate it !! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help w/Duplicates & Macros
i think this will work ...i'll have to wait till monday to test at work
....thanks!!!! cheers, mayte "Vergel Adriano" wrote: Mayte, maybe something like this... Sub test() Dim lRow As Long Dim lLastRow As Long With ActiveSheet lLastRow = .Range("A1").End(xlDown).Row For lRow = lLastRow To 2 Step -1 If .Range("A" & lRow).Value = .Range("A" & lRow - 1).Value Then With ActiveSheet .Range("D" & lRow - 1).Value = .Range("D" & lRow - 1).Value + _ .Range("D" & lRow).Value .Range("E" & lRow - 1).Value = .Range("E" & lRow - 1).Value + _ .Range("E" & lRow).Value .Range("F" & lRow - 1).Value = .Range("F" & lRow).Value .Range("G" & lRow - 1).Value = .Range("G" & lRow).Value .Rows(lRow).EntireRow.Delete End With End If Next lRow End With End Sub -- Hope that helps. Vergel Adriano "Mayte" wrote: anyway to delete the duplicate files (File #) but leaving the open date & open time of the original (first opened file) + close date & close time from the last opened file ..?? ...including a total sum per all files for CH & Issuer?? File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 1 1 04/01/08 07:30 A100444 04/02/08 05:00 3 0 04/03/08 13:15 A100445 04/01/08 08:00 1 0 04/01/08 08:30 A100445 04/02/08 08:30 1 1 04/03/08 16:00 A100445 04/03/08 08:45 1 1 04/05/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 Result will look like this... File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 4 2 04/01/08 13:15 A100445 04/01/08 08:00 3 2 04/01/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 I got helped last time and managed to use a macro to delete duplicates with open date and time but now I need the sum of all files (including duplicates) for CH and Issuer + the close date and time of last filed open ..make sense ... any ideas will be greatly appreciate it !! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help w/Duplicates & Macros
Mayte,
maybe offtopic but i wouldn't be surprised if you've never used pivottables. it takes a bit of time to comfortably use pivottables, but then they are a great, flexible timesaver. cheerz! Jurgen aka keepITcool -- Mayte wrote: i think this will work ...i'll have to wait till monday to test at work ...thanks!!!! cheers, mayte "Vergel Adriano" wrote: |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help w/Duplicates & Macros
it's keeping the first file # open time & date and the last file # close date
& time perfectly .....but it's not adding up the # of CH and Issuer ...any ideas how to do that?? "Mayte" wrote: anyway to delete the duplicate files (File #) but leaving the open date & open time of the original (first opened file) + close date & close time from the last opened file ..?? ...including a total sum per all files for CH & Issuer?? File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 1 1 04/01/08 07:30 A100444 04/02/08 05:00 3 0 04/03/08 13:15 A100445 04/01/08 08:00 1 0 04/01/08 08:30 A100445 04/02/08 08:30 1 1 04/03/08 16:00 A100445 04/03/08 08:45 1 1 04/05/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 Result will look like this... File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 4 2 04/01/08 13:15 A100445 04/01/08 08:00 3 2 04/01/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 I got helped last time and managed to use a macro to delete duplicates with open date and time but now I need the sum of all files (including duplicates) for CH and Issuer + the close date and time of last filed open ..make sense ... any ideas will be greatly appreciate it !! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help w/Duplicates & Macros
Never mind ... it's working perfectly !!! I made a mistake when copying ...so
sorry !! thanks a lot Verge Adriano!! Cheers, Mayte "Vergel Adriano" wrote: Mayte, maybe something like this... Sub test() Dim lRow As Long Dim lLastRow As Long With ActiveSheet lLastRow = .Range("A1").End(xlDown).Row For lRow = lLastRow To 2 Step -1 If .Range("A" & lRow).Value = .Range("A" & lRow - 1).Value Then With ActiveSheet .Range("D" & lRow - 1).Value = .Range("D" & lRow - 1).Value + _ .Range("D" & lRow).Value .Range("E" & lRow - 1).Value = .Range("E" & lRow - 1).Value + _ .Range("E" & lRow).Value .Range("F" & lRow - 1).Value = .Range("F" & lRow).Value .Range("G" & lRow - 1).Value = .Range("G" & lRow).Value .Rows(lRow).EntireRow.Delete End With End If Next lRow End With End Sub -- Hope that helps. Vergel Adriano "Mayte" wrote: anyway to delete the duplicate files (File #) but leaving the open date & open time of the original (first opened file) + close date & close time from the last opened file ..?? ...including a total sum per all files for CH & Issuer?? File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 1 1 04/01/08 07:30 A100444 04/02/08 05:00 3 0 04/03/08 13:15 A100445 04/01/08 08:00 1 0 04/01/08 08:30 A100445 04/02/08 08:30 1 1 04/03/08 16:00 A100445 04/03/08 08:45 1 1 04/05/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 Result will look like this... File # OPEN Date OPEN Time CH Issuer CLOSE Date CLOSE Time A100444 04/01/08 07:00 4 2 04/01/08 13:15 A100445 04/01/08 08:00 3 2 04/01/08 23:59 A100446 04/01/08 09:00 1 0 04/01/08 10:00 A100449 04/03/08 10:00 2 2 04/03/08 22:00 I got helped last time and managed to use a macro to delete duplicates with open date and time but now I need the sum of all files (including duplicates) for CH and Issuer + the close date and time of last filed open ..make sense ... any ideas will be greatly appreciate it !! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
| Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
| Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
| choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
| weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
Linear Mode
