Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
start and end date on different rows
I have a report imported from a database which contains holiday dates for
staff but in different rows and with duplicates. How can I merge the dates into one row and get rid of duplicates? For example: A B C Staff ID Start Date End Date 1 1234567 13/02/08 19/02/08 2 1234567 20/02/08 28/02/08 3 1234567 26/02/08 28/02/08 4 1234567 07/10/08 08/10/08 Rows 1&2 is the same holiday and should be on one row with a start of 13/02/08 and an end of 28/02/08, Row 3 is a duplicate and should be ignored(removed), Row 4 should be kept. Is this possible? Grateful for any help, I'm on Excel 2000 (sadly...) at work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
start and end date on different rows
Do you want the earliest start date and the latest end date from the
differrent rows? Does you data go down the worksheet for various different staff ID's 1) Use a macro 2) add a formula to an auxilary column which will get the min start date and max end date. Then manually delete the extra rows. "jon boy" wrote: I have a report imported from a database which contains holiday dates for staff but in different rows and with duplicates. How can I merge the dates into one row and get rid of duplicates? For example: A B C Staff ID Start Date End Date 1 1234567 13/02/08 19/02/08 2 1234567 20/02/08 28/02/08 3 1234567 26/02/08 28/02/08 4 1234567 07/10/08 08/10/08 Rows 1&2 is the same holiday and should be on one row with a start of 13/02/08 and an end of 28/02/08, Row 3 is a duplicate and should be ignored(removed), Row 4 should be kept. Is this possible? Grateful for any help, I'm on Excel 2000 (sadly...) at work. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
start and end date on different rows
Sort your rows first then use this, it uses a helper column D so i am assuming you don't have data in column D: Code: -------------------- Sub DeleteDuplicateRowsPt2() Dim lngLastRowMarker As Long On Error GoTo ErrHandler lngLastRowMarker = Range("A65536").End(xlUp).Row Application.ScreenUpdating = False Range("D2").Formula = "=IF(RC[-2]&RC[-1]=R[-1]C[-2]&R[-1]C[-1],2,1)" Range("D2").Copy Range("D3:D" & lngLastRowMarker).Select ActiveSheet.Paste Application.CutCopyMode = False Range("D2:D" & lngLastRowMarker).Copy Range("D2:D" & lngLastRowMarker).PasteSpecial (xlPasteValues) Application.CutCopyMode = False Range("A2:D" & lngLastRowMarker).Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D").Select Selection.Find(What:="2", after:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.EntireRow.Delete Columns("D").Delete Range("A2").Select MsgBox "All the duplicated rows have now been removed.", vbInformation, "Delete Duplicate Rows Editor" Application.ScreenUpdating = True Exit Sub ErrHandler: MsgBox "There were no duplicate row(s) to delete!", vbExclamation, "Delete Duplicate Rows Editor" Columns("D").Delete Range("A2").Select Application.ScreenUpdating = True End Sub -------------------- jon boy;196896 Wrote: I have a report imported from a database which contains holiday dates for staff but in different rows and with duplicates. How can I merge the dates into one row and get rid of duplicates? For example: A B C Staff ID Start Date End Date 1 1234567 13/02/08 19/02/08 2 1234567 20/02/08 28/02/08 3 1234567 26/02/08 28/02/08 4 1234567 07/10/08 08/10/08 Rows 1&2 is the same holiday and should be on one row with a start of 13/02/08 and an end of 28/02/08, Row 3 is a duplicate and should be ignored(removed), Row 4 should be kept. Is this possible? Grateful for any help, I'm on Excel 2000 (sadly...) at work. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54252 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating monthly start date with a start date | Excel Worksheet Functions | |||
Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
Minimum Date Greater Than Observation Start Date | Excel Discussion (Misc queries) | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel |