Removing Duplicates

[Kandy1084]

Is there a formula (or other way) to identify and remove both duplicates and the original value of a duplicate (found only in columns A and B)?
I've attached an example of what I am working with.

The end result would be the entire Row 6 and Row 7 (both have duplicates in column A and B) being deleted
and entire Row 9 and Row 10 being deleted.

Thanks in advance!Duplicates.xlsx

[pjwhitfield]

=IF(OR(AND(A4=A3,B4=B3),A4=A5,B4=B5),"X","")

In D4 and then copied down will populate the X's for you. If you're not bothered about the deletions being automated then filtering on X and delete will remove them.

Note however, this formula will put an X in all 4 of the rows, whereas your original data only had it in the duplicated row, your blurb however states you want to delete all 4 rows. If you dont, and you only want to delete the duplicated entry ie rows 7 and 10 then use:

=IF(AND(A4=A3,B4=B3),"X","")

[JohnTopley]

Try .. in F4

=IFERROR(INDEX(A$4:A$11,SMALL(IF(COUNTIF($A$4:$A$11,$A$4:$A$11)=1,ROW($A$4:$A$11)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

Enter with Ctrl+Shift+ENTER

Then copy across and down

[Kandy1084]

Actually John,
I need this to continue to work only if the first AND second column both contain the duplicates.
The values in the first column can appear with different values in the second column and I would need them to appear in the formulated result.
My error for attaching a poor example.
Can this still be accomplished?

[Kandy1084]

Thank you, PJWitfield, the first formula does help to accomplish what I am looking to do!

However, is there any other way to get this result without the extra steps of filtering and manually deleting?

Thanks

[FlameRetired]

....... However, is there any other way to get this result without the extra steps of filtering and manually deleting? ........

Did you try JohnTopley's formula? It does that.

[nahidking]

=IF(A4=A5,"X",IF(A4=A3,"X",""))

u can use this simple formula in D column to get X's..

[Shareez Saleem]

Select the cells and use the "Remove Duplicates" option under "Data" Tab.

[Kandy1084]

My apologies, John Topley is correct.
I'm not sure what I did wrong the first time, but this works perfectly for me!
Thanks for your help!

[JohnTopley]

Try ...

=IFERROR(INDEX(A$4:A$11,SMALL(IF(COUNTIFS($A$4:$A$11,$A$4:$A$11,$B$4:$B$11,$B$4:$B$11)=1,ROW($A$4:$A$11)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")


Enter with Ctrl+Shift+ENTER

Then copy across and down

You did state A and B in your original post so "mea culpa".

[Kandy1084]

Perfect, thanks so much!!

[Czeslaw]

I offer a version without an array formula.