Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Files | Links and Linking in Excel | |||
Find Duplicate Values and Return Another Value | Excel Worksheet Functions | |||
UDFs return #NAME error sometimes, other times, they work | Excel Discussion (Misc queries) | |||
how to return a number for how many times a word is used in excel | Excel Worksheet Functions | |||
How do I duplicate a sheet 20 times in an excel spreadsheet | Excel Worksheet Functions |