Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Delete Duplicate rows based on multiple criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete Duplicate rows based on multiple criteria

    Hello, good people of Ozgrid!

    I have a question on the deleting rows with multiple criteria. I have attached a sample workbook with a description on what I am trying to achieve.

    Can please someone look at it and let me know if this is possible to do?

    As far as I understand the number of records can vary from 2000 to 15000 records if it matters (probably it will affect how fast the macro will be executed).

    Any help is greatly appreciated. Thanks!

    Nat
    Attached Files

  • #2
    Re: Delete Duplicate rows based on multiple criteria

    Hi

    Is this right.
    A duplicate has the same:
    Part#, Issue Indicator, Rule Check#, Issue Description, Layout Part#, Program
    You then have a priority ranking based on:
    1) Approved out ranks blanks in column I
    2) Non blanks in column I (regardless of entry) take the most recent date in column H
    3) All blanks in column I, keep the most recent date

    This would result in keeping rows 2, 5 and 7 in the sample file. Is this correct???


    Tony

    Comment


    • #3
      Re: Delete Duplicate rows based on multiple criteria

      Hi, Tony,

      Originally posted by acw
      Is this right.
      A duplicate has the same:
      Part#, Issue Indicator, Rule Check#, Issue Description, Layout Part#, Program
      Yes, it is right

      You then have a priority ranking based on:
      1) Approved out ranks blanks in column I
      Yes, if it is approved, I would like all other blanks with the same criteria as above to be deleted.

      2) Non blanks in column I (regardless of entry) take the most recent date in column H
      Correct, so if it is unapproved at a later day, I would like to see the latest action

      3) All blanks in column I, keep the most recent date
      After checking the previous two conditions, yes it is correct.

      This would result in keeping rows 2, 5 and 7 in the sample file. Is this correct???
      Yes, you are right, rows 2, 5, 7 are the ones I would like to keep (Actually in the example, I have row 6 to keep, but that is wrong). You got to the heart of the problem

      I hope it is possible to do through the VBA code. I would like to add it to my Add-In, if possible.

      Thank you very much for responding!

      Natalie

      Comment


      • #4
        Re: Delete Duplicate rows based on multiple criteria

        Hi

        A couple of things.
        1) The output should be rows 2,5,7 and 8 (there is a different description).
        2) You will have to make sure the entries in column H are all dates. Both the 28/10/05 entries are curently text - they have to be changed.
        3) I'm not sure about the hierarchy when you have 2 non blank entries. Can you please review the last ElseIf statement.



        Code:
        Sub aaa()
         Sheets("Formatted Data").Select
         Call Sorter
        'step through the data and evaluate each pair of entries
         For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        'Evaluates the current row and the row above and see if there is a primary match
        'If no primary match, then the row is unique and is not deleted
          Tester = Evaluate("=and(A" & i & ":F" & i & "=A" & i - 1 & ":F" & i - 1 & ")")
        
          If Tester Then 'there is a pair of primary matching rows
           If IsEmpty(Cells(i, "I")) And IsEmpty(Cells(i - 1, "I")) Then
        'both column I entries are empty, so delete the current row.  Sorting primary Col I descending
            Cells(i, "A").EntireRow.Delete
           ElseIf Cells(i, "I") = "Approve" And IsEmpty(Cells(i - 1, "I")) Then
        'Current Cell is Approve and other row blank
            Cells(i - 1, "A").EntireRow.Delete
            i = i - 1
           ElseIf IsEmpty(Cells(i, "I")) And Cells(i - 1, "I") = "Approve" Then
        'Other row Approve, delete current row
            Cells(i, "A").EntireRow.Delete
           ElseIf Not (IsEmpty(Cells(i, "I"))) And Not (IsEmpty(Cells(i - 1, "I"))) Then
        'current row blank, other row not blank
            Cells(i, "A").EntireRow.Delete
           End If
         End If
         Next i
        End Sub
        Sub Sorter()
        'Sorts the data prior to evaluating
            
            Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Row).Select
            Selection.Sort Key1:=Range("H2"), Order1:=xlDescending, Key2:=Range("I2") _
                , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
                , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
                xlSortNormal
            Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("E2") _
                , Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending, Header:= _
                xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
                xlSortNormal
            Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
                , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
                xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
                xlSortNormal
        End Sub

        Tony

        Comment


        • #5
          Re: Delete Duplicate rows based on multiple criteria

          Hi, Tony

          I would like to thank you for helping me out. And here are my answers to your comments.

          Originally posted by acw
          A couple of things.
          1) The output should be rows 2,5,7 and 8 (there is a different description).
          You are right. The record 8 should be kept as well.

          Originally posted by acw
          2) You will have to make sure the entries in column H are all dates. Both the 28/10/05 entries are curently text - they have to be changed.
          OK. No problem there. I will make them dates.

          Originally posted by acw
          3) I'm not sure about the hierarchy when you have 2 non blank entries. Can you please review the last ElseIf statement.
          Regarding the non-blank entries, if there are 2 non-blank entries, I would like to keep the most recent one. Let's say change was

          approved 10-28-2005, and then
          unapproved 11-01-2005

          I would like to keep the latter.

          I tried the code. And gives me the Run-Time Error '1004' when it gets to the first filter selection.

          Also, another question is will this work if there are more than two entries of the same record?

          Thank you very much for taking time for helping me. I appreciate it a lot.

          Natalie
          Last edited by nat_m; November 2nd, 2005, 22:25.

          Comment


          • #6
            Re: Delete Duplicate rows based on multiple criteria

            Nat

            1) Current code will keep the more recent entry for the both non blanks.
            2) What version of Excel are you using? Did you run this code in the sample file you pasted on the site? If not, then can you upload the file that you used to get the error.
            3) Should do. The sort should put the most recent date first so as this processes from the bottom up, the top most entry for the group should have the newest date.


            Tony

            Comment


            • #7
              Re: Delete Duplicate rows based on multiple criteria

              Hi, Tony.

              Originally posted by acw
              1) Current code will keep the more recent entry for the both non blanks.
              This is exactly what I want

              Originally posted by acw
              2) What version of Excel are you using? Did you run this code in the sample file you pasted on the site? If not, then can you upload the file that you used to get the error.
              I use Excel 2000. Yes, I did try it on the sample data, and I still got the error message, when it came to the selection portion of the sub:

              Run-time error '1004':
              Application-defined or object-defined error


              Originally posted by acw
              3) Should do. The sort should put the most recent date first so as this processes from the bottom up, the top most entry for the group should have the newest date.
              Great!!! So, the only thing remains is to get it to work in my workbook

              Thanks for your help, Tony.

              Nat

              Comment


              • #8
                Re: Delete Duplicate rows based on multiple criteria

                Nat

                Interesting. Refer to the PMs.


                Tony

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X