start and end date on different rows

[jon boy]

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.

 

[Joel]

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.

 

[Simon Lloyd]

Sort your rows first then use this, it uses a helper column D so i a
assuming you don't have data in column D

Code
-------------------
Sub DeleteDuplicateRowsPt2(
Dim lngLastRowMarker As Lon
On Error GoTo ErrHandle
lngLastRowMarker = Range("A65536").End(xlUp).Ro
Application.ScreenUpdating = Fals
Range("D2").Formula = "=IF(RC[-2]&RC[-1]=R[-1]C[-2]&R[-1]C[-1],2,1)
Range("D2").Cop
Range("D3" & lngLastRowMarker).Selec
ActiveSheet.Past
Application.CutCopyMode = Fals
Range("D2" & lngLastRowMarker).Cop
Range("D2" & lngLastRowMarker).PasteSpecial (xlPasteValues
Application.CutCopyMode = Fals
Range("A2" & lngLastRowMarker).Selec
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNorma
Columns("D").Selec
Selection.Find(What:="2", after:=ActiveCell, LookIn:=xlFormulas, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
False).Activat
Range(ActiveCell, ActiveCell.End(xlDown)).Selec
Selection.EntireRow.Delet
Columns("D").Delet
Range("A2").Selec
MsgBox "All the duplicated rows have now been removed.", vbInformation, "Delete Duplicate Rows Editor
Application.ScreenUpdating = Tru
Exit Su
ErrHandler
MsgBox "There were no duplicate row(s) to delete!", vbExclamation, "Delete Duplicate Rows Editor
Columns("D").Delet
Range("A2").Selec
Application.ScreenUpdating = Tru

End Su

-------------------

I have a report imported from a database which contains holiday date
fo
staff but in different rows and with duplicates. How can I merge th
date
into one row and get rid of duplicates

For example
A B
Staff ID Start Date End Dat
1 1234567 13/02/08 19/02/0
2 1234567 20/02/08 28/02/0
3 1234567 26/02/08 28/02/0
4 1234567 07/10/08 08/10/0

Rows 1&2 is the same holiday and should be on one row with a start o
13/02/08 and an end of 28/02/08, Row 3 is a duplicate and should b
ignored(removed), Row 4 should be kept

Is this possible? Grateful for any help, I'm on Excel 2000 (sadly...
a
work

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com