Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Delete Duplicate rows based on multiple criteria

  1. #1
    Join Date
    18th October 2005
    Location
    Ontario, Canada
    Posts
    187

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th May 2005
    Posts
    1,036

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th October 2005
    Location
    Ontario, Canada
    Posts
    187

    Re: Delete Duplicate rows based on multiple criteria

    Hi, Tony,

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    6th May 2005
    Posts
    1,036

    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.



    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th October 2005
    Location
    Ontario, Canada
    Posts
    187

    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.

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

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

    Quote 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 at 21:25.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    6th May 2005
    Posts
    1,036

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th October 2005
    Location
    Ontario, Canada
    Posts
    187

    Re: Delete Duplicate rows based on multiple criteria

    Hi, Tony.

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

    Quote 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


    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Delete Duplicate rows based on multiple criteria

    Nat

    Interesting. Refer to the PMs.


    Tony

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete Duplicate Rows Based On 1 Column
    By kingjos in forum EXCEL HELP
    Replies: 8
    Last Post: June 12th, 2008, 22:25
  2. Delete Opposite Rows Based On Multiple Criteria
    By NCSUAaron in forum EXCEL HELP
    Replies: 4
    Last Post: March 1st, 2008, 09:22
  3. Delete Rows Based On Multiple Criteria
    By mcbeck in forum EXCEL HELP
    Replies: 1
    Last Post: August 9th, 2007, 10:56
  4. Replies: 7
    Last Post: April 25th, 2007, 04:48
  5. DELETE ROWS BASED ON CRITERIA
    By Ladybug in forum EXCEL HELP
    Replies: 22
    Last Post: October 31st, 2005, 23:37

Bookmarks

Posting Permissions

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