Return Time for Duplicate Files & Times
 

I'm trying to come up with an easier way to do a report .... I have to go by
File Number Date and Closed Time I need to get per each file the Open Date
(first time it was opened) and the Close time (last call back). The problem
is that a unique file number can be opened several times so it gets
duplicated and also there are 4 call back columns which makes it harder
because the last call back is the close date but callback 4 is not always the
last one. There could be only 1 callback or 4 ...?? any ideas ?? don't even
know if this is possible ..??

Here's sample of the table

File # Date Callback 1 Callback 2 Callback 3 Callback 4
A10055444 04/01/08 07:00 07:45 08:00 10:30
A10055445 04/01/08 08:00 08:30 11:00 12:00
A10055446 04/01/08 07:30 08:00 - -
A10055444 04/02/08 13:00 13:15 14:00 15:45
A10055445 04/02/08 14:00 16:00 16:30 18:00
A10055445 04/03/08 22:00 - - -
A10055449 04/03/08 06:00 07:00 - -
A10055444 04/03/08 16:00 18:00 18:15 -

I should get something like this
File # Date Closed
A10055444 04/01/08 15:45
A10055445 04/01/08 22:00
A10055446 04/01/08 08:00
A10055449 04/03/08 07:00


Cheers,
Mayte

Return Time for Duplicate Files & Times
 

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
If LastCol 3 Then

.Cells(i, "C").Value = .Cells(i, LastCol).Value
.Cells(i, "D").Resize(LastCol - 3).ClearContents
End If
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mayte" wrote in message
...
I'm trying to come up with an easier way to do a report .... I have to go
by
File Number Date and Closed Time I need to get per each file the Open
Date
(first time it was opened) and the Close time (last call back). The
problem
is that a unique file number can be opened several times so it gets
duplicated and also there are 4 call back columns which makes it harder
because the last call back is the close date but callback 4 is not always
the
last one. There could be only 1 callback or 4 ...?? any ideas ?? don't
even
know if this is possible ..??

Here's sample of the table

File # Date Callback 1 Callback 2 Callback 3 Callback 4
A10055444 04/01/08 07:00 07:45 08:00 10:30
A10055445 04/01/08 08:00 08:30 11:00 12:00
A10055446 04/01/08 07:30 08:00 - -
A10055444 04/02/08 13:00 13:15 14:00 15:45
A10055445 04/02/08 14:00 16:00 16:30 18:00
A10055445 04/03/08 22:00 - - -
A10055449 04/03/08 06:00 07:00 - -
A10055444 04/03/08 16:00 18:00 18:15 -

I should get something like this
File # Date Closed
A10055444 04/01/08 15:45
A10055445 04/01/08 22:00
A10055446 04/01/08 08:00
A10055449 04/03/08 07:00


Cheers,
Mayte

Return Time for Duplicate Files & Times
 

Bob - you are an ACE !!! this is great !!! and one more thing if it's not too
much ... I forgot that I also need the date , I need the first date the file
was opened to be populated ..can that be done?

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
If LastCol 3 Then

.Cells(i, "C").Value = .Cells(i, LastCol).Value
.Cells(i, "D").Resize(LastCol - 3).ClearContents
End If
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mayte" wrote in message
...
I'm trying to come up with an easier way to do a report .... I have to go
by
File Number Date and Closed Time I need to get per each file the Open
Date
(first time it was opened) and the Close time (last call back). The
problem
is that a unique file number can be opened several times so it gets
duplicated and also there are 4 call back columns which makes it harder
because the last call back is the close date but callback 4 is not always
the
last one. There could be only 1 callback or 4 ...?? any ideas ?? don't
even
know if this is possible ..??

Here's sample of the table

File # Date Callback 1 Callback 2 Callback 3 Callback 4
A10055444 04/01/08 07:00 07:45 08:00 10:30
A10055445 04/01/08 08:00 08:30 11:00 12:00
A10055446 04/01/08 07:30 08:00 - -
A10055444 04/02/08 13:00 13:15 14:00 15:45
A10055445 04/02/08 14:00 16:00 16:30 18:00
A10055445 04/03/08 22:00 - - -
A10055449 04/03/08 06:00 07:00 - -
A10055444 04/03/08 16:00 18:00 18:15 -

I should get something like this
File # Date Closed
A10055444 04/01/08 15:45
A10055445 04/01/08 22:00
A10055446 04/01/08 08:00
A10055449 04/03/08 07:00


Cheers,
Mayte

Return Time for Duplicate Files & Times
 

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

If .Cells(i, "A").Value < .Cells(i - 1, "A").Value Then

.Cells(i + 1, "C").Value = .Cells(i, "C").Value
End If
.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
'If LastCol 4 Then

.Cells(i, "D").Value = .Cells(i, LastCol).Value
If LastCol 4 Then
.Cells(i, "E").Resize(LastCol - 4).ClearContents
End If
'End If
End If
Next i

.Range("C1:F1").Value = Array("Opened", "Closed", "", "")
.Columns("C:D").NumberFormat = "hh:mm"
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mayte" wrote in message
...
Bob - you are an ACE !!! this is great !!! and one more thing if it's not
too
much ... I forgot that I also need the date , I need the first date the
file
was opened to be populated ..can that be done?

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
If LastCol 3 Then

.Cells(i, "C").Value = .Cells(i, LastCol).Value
.Cells(i, "D").Resize(LastCol - 3).ClearContents
End If
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Mayte" wrote in message
...
I'm trying to come up with an easier way to do a report .... I have to
go
by
File Number Date and Closed Time I need to get per each file the Open
Date
(first time it was opened) and the Close time (last call back). The
problem
is that a unique file number can be opened several times so it gets
duplicated and also there are 4 call back columns which makes it harder
because the last call back is the close date but callback 4 is not
always
the
last one. There could be only 1 callback or 4 ...?? any ideas ?? don't
even
know if this is possible ..??

Here's sample of the table

File # Date Callback 1 Callback 2 Callback 3 Callback 4
A10055444 04/01/08 07:00 07:45 08:00 10:30
A10055445 04/01/08 08:00 08:30 11:00 12:00
A10055446 04/01/08 07:30 08:00 - -
A10055444 04/02/08 13:00 13:15 14:00 15:45
A10055445 04/02/08 14:00 16:00 16:30 18:00
A10055445 04/03/08 22:00 - - -
A10055449 04/03/08 06:00 07:00 - -
A10055444 04/03/08 16:00 18:00 18:15 -

I should get something like this
File # Date Closed
A10055444 04/01/08 15:45
A10055445 04/01/08 22:00
A10055446 04/01/08 08:00
A10055449 04/03/08 07:00


Cheers,
Mayte

Return Time for Duplicate Files & Times
 

so GRAND !!!!! Cheers mate !!

Thanks,
Mayte

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

If .Cells(i, "A").Value < .Cells(i - 1, "A").Value Then

.Cells(i + 1, "C").Value = .Cells(i, "C").Value
End If
.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
'If LastCol 4 Then

.Cells(i, "D").Value = .Cells(i, LastCol).Value
If LastCol 4 Then
.Cells(i, "E").Resize(LastCol - 4).ClearContents
End If
'End If
End If
Next i

.Range("C1:F1").Value = Array("Opened", "Closed", "", "")
.Columns("C:D").NumberFormat = "hh:mm"
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mayte" wrote in message
...
Bob - you are an ACE !!! this is great !!! and one more thing if it's not
too
much ... I forgot that I also need the date , I need the first date the
file
was opened to be populated ..can that be done?

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:F").Sort key1:=.Range("A1"), header:=xlYes
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

.Rows(i).Delete
Else

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
If LastCol 3 Then

.Cells(i, "C").Value = .Cells(i, LastCol).Value
.Cells(i, "D").Resize(LastCol - 3).ClearContents
End If
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Mayte" wrote in message
...
I'm trying to come up with an easier way to do a report .... I have to
go
by
File Number Date and Closed Time I need to get per each file the Open
Date
(first time it was opened) and the Close time (last call back). The
problem
is that a unique file number can be opened several times so it gets
duplicated and also there are 4 call back columns which makes it harder
because the last call back is the close date but callback 4 is not
always
the
last one. There could be only 1 callback or 4 ...?? any ideas ?? don't
even
know if this is possible ..??

Here's sample of the table

File # Date Callback 1 Callback 2 Callback 3 Callback 4
A10055444 04/01/08 07:00 07:45 08:00 10:30
A10055445 04/01/08 08:00 08:30 11:00 12:00
A10055446 04/01/08 07:30 08:00 - -
A10055444 04/02/08 13:00 13:15 14:00 15:45
A10055445 04/02/08 14:00 16:00 16:30 18:00
A10055445 04/03/08 22:00 - - -
A10055449 04/03/08 06:00 07:00 - -
A10055444 04/03/08 16:00 18:00 18:15 -

I should get something like this
File # Date Closed
A10055444 04/01/08 15:45
A10055445 04/01/08 22:00
A10055446 04/01/08 08:00
A10055449 04/03/08 07:00


Cheers,
Mayte