Return Time for Duplicate Files & Times

[Mayte]

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

 

[Bob Phillips]

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]

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]

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").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]

so GRAND !!!!! Cheers mate !!

Thanks,
Mayte

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").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)