Combining multiple dates so only the first and last date are provided

[arnarbi]

Hi!

I have a list of drug prescriptions that I'm trying to categorize.
The list includes: name of patient, drug prescribed, date of prescription, and how long the prescription lasts.
Each person has multiple drug prescriptions and may even change his prescribed drug during the study period.
Is there some way to combine the data so that for each person only the date he starts taking the drug and the last day he takes the drug are included?

Example:
Name Drug Prescription date Length
Andy Warfarin 01.01.2016 30 days
Andy Warfarin 02.01.2016 30 days
Andy Warfarin 03.01.2016 30 days
Andy Warfarin 04.01.2016 30 days
Ben Rivaroxaban 04.04.2016 60 days
Ben Rivaroxaban 06.04.2016 60 days
Ben Warfarin 08.04.2016 30 days
Ben Warfarin 09.04.2016 30 days
Julie Edoxaban 01.01.2016 40 days
Julie Edoxaban 02.11.2016 40 days
Julie Rivaroxaban 03.21.2016 60 days

Would become:
Name Drug Starting date End date
Andy Warfarin 01.01.2016 05.01.2016 (04.01.2016 + 30 days)
Ben Rivaroxaban 04.04.2016 08.04.2016
Ben Warfarin 08.04.2016 10.04.2016
Julie Edoxaban 01.01.2016 03.21.2016
Julie Rivaroxaban 03.21.2016 05.21.2016

Thanks in advance,
Arnar

 

[sheetspread]

pivot table

 

[footoo]

Perhaps :

Sub FT()
Dim rng As Range
Application.ScreenUpdating = False
ActiveSheet.Copy Before:=ActiveSheet
Set rng = Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Formula = "=IF(A2<>A1,C2,IF(A2<>A3,C2,0/0))"
rng = rng.Value
rng.SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
Set rng = Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Formula = "=IF(AND(A2<>A1,A2<>A3),C2,IF(A2=A3,E3,0/0))"
rng = rng.Value
rng.SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
Application.ScreenUpdating = False
End Sub