Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    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. #3

    Question

    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.PNG 
Views:	2 
Size:	7.4 KB 
ID:	2828  
    Attached Files Attached Files
    Last edited by ajtex; 2014-11-12 at 09:34 AM.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    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. #5
    Morning Bob, Fantastic! that did it! I thank you so much.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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.
    Attached Files Attached Files

  7. #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. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ajtex View Post
    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
    Last edited by Hercules1946; 2014-11-18 at 04:25 PM.

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    sorry cant get HTML to work
    Last edited by Hercules1946; 2014-11-18 at 04:35 PM.

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Here's my data as macro free:

    HTH

    Hercules
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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