Deleting rows with date ranges between a set of duplicate name rows with dates higher

[ajtex]

Good day

I'm all new to this and don't realy know a lot about Excel's formulas or any other great functions.

I have a spreadsheet with about 3400 rows. I need to filter or delete all rows of patient numbers, where a patient has got more than 1 evaluation done on different dates. So, I basically only need the first evaluation date row and the last evaluation date row. example: where there was an Admission evaluation done, then with 4 interrim evaluations and an Discharge evaluation. I only need the Admission & Discharge dates.

in Column A: PatientNumber
in Column K: ScoreDate (Evaluaion Date) (lowest date 2006-12-07 and highest date 2014-11-07)

Example:

patientnumber	score_date
TV1834	2014-10-05
TV1834	2014-10-06
TV1834	2014-10-08
TV1834	2014-10-13
TV1834	2014-10-15

Any help would be so much appreciated !

Kind Regards

 

[Bob Phillips]

In a third column, give row 1 a heading and in row 2 add this array formula (adjust ranges to suit), and copy down

=OR(B2=MIN(IF(A2=$A$2:$A$20,$B$2:$B$20)),B2=MAX(IF(A2=$A$2:$A$20,$B$2:$B$20)))

Then just filter the third column for TRUE

 

[ajtex]

Good day Bob, thank you for replying to this thread. I followed your instructions as above, but unfortunately I keep getting the same error popup message.
Please see screenshot attached. I have also added a sample of the data file.

 

[Bob Phillips]

I didn't get that error. Didi you try adjusting the ranges and get something wrong perhaps?

Also, you don't have real dates, just text strings that look like dates, so you need to adjust my formula

=OR(--K2=MIN(IF(A2=$A$2:$A$25,--$K$2:$K$25)),--K2=MAX(IF(A2=$A$2:$A$25,--$K$2:$K$25)))

 

[ajtex]

Morning Bob, Fantastic! that did it! I thank you so much.

 

[Hercules1946]

Hello and sorry - i forgot to post my solution with all thats going on at the moment. This bit of code doesn't need any helper cols or filtering.

 

[ajtex]

Hey Hercules1946 - Thank you for your assistance! but could not open the file as it was blocked due to macros! Any ways could mannage.

 

[Hercules1946]

Hey Hercules1946 - Thank you for your assistance! but could not open the file as it was blocked due to macros! Any ways could mannage.

Thats a pity. Here is my data and the code used.

Sub Delete_UnwantedRows()
'This code deletes duplicate rows except the first and last
'instae based on Col 1 as key, plus data starting at row 2.
Dim i As Long
Dim j As Long
Dim DELETEDOK As Boolean
i = 2
Do While i <= ActiveSheet.UsedRange.Rows.Count
    DELETEDOK = False
    For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
        If Cells(i - 1, 1) <> Cells(i, 1) Then GoTo r1:
        If Cells(i, 1) = Cells(j, 1) Then
            Rows(i).Delete
            DELETEDOK = True
            Exit For
        End If
    Next j
r1:
    If Not DELETEDOK Then i = i + 1
Loop
End Sub

 

[Hercules1946]

sorry cant get HTML to work

 

[Hercules1946]

Here's my data as macro free:

HTH

Hercules

 

[ajtex]

morning Hercules, thank you for the macro free code... worked!!! I can't thank you enough!

 

[Hercules1946]

morning Hercules, thank you for the macro free code... worked!!! I can't thank you enough!

Glad to be of help and thanks for the feedback.

Hercules