Help w/Duplicates & Macros


M

Mayte

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 !!
 
Ad

Advertisements

V

Vergel Adriano

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
 
M

Mayte

i think this will work ...i'll have to wait till monday to test at work
....thanks!!!!

cheers,
mayte
 
K

keepITcool

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
 
M

Mayte

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??
 
Ad

Advertisements

M

Mayte

Never mind ... it's working perfectly !!! I made a mistake when copying ...so
sorry !!

thanks a lot Verge Adriano!!

Cheers,
Mayte
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top