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

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

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

2. 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

3. 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.

4. 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)))

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

6. 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.

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

8. Originally Posted by 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.
Thats a pity. Here is my data and the code used.

Code:
```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```

9. sorry cant get HTML to work

10. Here's my data as macro free:

HTH

Hercules

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•