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
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.
Please Login or Register to view this content.
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:
Please Login or Register to view this content.
If someone has helped you then please add to their Reputation
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
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
Did you try JohnTopley's formula? It does that.Originally Posted by Kandy1084
Last edited by FlameRetired; 08-24-2015 at 11:22 PM.
Dave
=IF(A4=A5,"X",IF(A4=A3,"X",""))
u can use this simple formula in D column to get X's..
Select the cells and use the "Remove Duplicates" option under "Data" Tab.
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!
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?
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".
Last edited by JohnTopley; 08-25-2015 at 11:41 AM.
Perfect, thanks so much!!
I offer a version without an array formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks