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.

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.

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