Delete Rows where data is the same but in different columns

[Howardc]

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

[Excel Fox]

So you want to delete all of those rows, or keep one unique?

[Excel Fox]

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

[Howardc]

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

[Excel Fox]

Howardc, you should use it as an array formula, as I had mentioned. You haven't done that in the attachment above.

[Howardc]

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/gnnc1g9ssq7cbf5/Deleting%20Rows%20Duplicate%20Values.zip


It would be appreciated if you would amend your formula to accommodate this.

[Excel Fox]

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?

[Howardc]

Hi Excel Fox

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

Once again thanks for your help

[Howardc]

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()


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/kc4sar8klw8eahb/Deleting%20Rows%20Duplicate%20Values.Macro.xls