Results 1 to 9 of 9

Thread: Delete Rows where data is the same but in different columns

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    Delete Rows where data is the same but in different columns

    I have values in Col F and G. Where the value in Col F is the same as in Col G and the control (ref #) in Col E is the same, then the rows contain this criteria are to be deleted

    I have highlighted the items in yellow where the values in Col F & G are the same and the control numbers match (i.e the control numbers are the same)

    I have attempted a macro, but cannot get it to work

    Your assistance is most appreciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    So you want to delete all of those rows, or keep one unique?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    OK, after further look, it seems that you just want to delete all records.

    If you use the following array formula in Range I2, and copy down, you'll get a TRUE (to keep rows) and FALSE (to delete rows)

    =ISNA(IF(ISBLANK(F2),MATCH(E2&G2,$E$2:$E$75&$F$2:$ F$75,),MATCH(E2&F2,$E$2:$E$75&$G$2:$G$75,)))

    And then you can filter all the FALSEs and delete the entire rows
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Excel Fox

    Thanks for your reply. The formula is giving me false for all the items. The items in Yellow should give me true

    Your assistance in resolving this will be most appreciated
    Attached Files Attached Files

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Howardc, you should use it as an array formula, as I had mentioned. You haven't done that in the attachment above.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Excel Fox

    Thanks for letting me know. I have now used the formula as an array. It is howver, not giving me the correct solution

    What Is need is where the value in Col F & G match for Eg F8 is 60.63 and G18 is 60.63 and the reference is the same i.e BR3P11/0001, then true must be returned

    https://www.dropbox.com/s/gnnc1g9ssq...e%20Values.zip


    It would be appreciated if you would amend your formula to accommodate this.
    Last edited by Howardc; 04-20-2014 at 11:33 AM.

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Howardc,

    Yes, I had already noticed that, and that's because the value in F8 is 60.63, however, the value in G18 is 60.634. It will require a slightly more complex formula to round all your numbers to 2 decimals, and then do a match. Do you want to do that, or do you want to standardize your data to common decimals first?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Excel Fox

    Thanks for picking this up. I have amended the values and it now works perfectly.

    Once again thanks for your help

  9. #9
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Excel Fox

    I have tried to automate your formula using VBA code, but it returns true for all the items

    Sub Dup_Val_SameRef()

    Code:
         Dim LR As Long
        LR = Cells(Rows.Count, "D").End(xlUp).Row
           Range("I2:I" & LR).ClearContents
       
        Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(RC[-3]),MATCH(RC[-4]&RC[-2],RC[-4]:R[398]C[-4]&RC[-3]:R[398]C[-3],0),MATCH(RC[-4]&RC[-3],RC[-4]:R[398]C[-4]&RC[-2]:R[398]C[-2],0)))"
    End Sub
    It would be appreciated if you could assist me in resolving this

    https://www.dropbox.com/s/kc4sar8klw...lues.Macro.xls

Similar Threads

  1. seprate data in rows to columns
    By CORAL in forum Excel Help
    Replies: 2
    Last Post: 03-10-2014, 08:18 AM
  2. search for data and delete rows containing it
    By RickStewart in forum Excel Help
    Replies: 2
    Last Post: 02-24-2014, 09:12 PM
  3. Replies: 10
    Last Post: 02-18-2014, 01:34 PM
  4. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  5. Replies: 5
    Last Post: 07-11-2013, 07:31 AM

Posting Permissions

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