Removing duplicate accounts listed in one sheet from another sheet

New Contributor

The first sheet that contains all account numbers used and a second sheet that includes specific account numbers.  I want to be able to remove any account numbers that are in the Second Sheet from the First Sheet. I can do manual search on the First sheet but want to be able to do it all at once is possible.  

4 Replies
best response confirmed by Hans Vogelaar (MVP)



An easy approach could be this formula. After applying the formula you can copy column B and paste only values. Then you can delete column A.

remove accounts in first sheet.JPG 

Thank you but unfortunately my list of account numbers do not line up so easily so the formula you propose does not solve my problem. The listings are random account numbers on each sheet


=IF(ISNA(MATCH('First Sheet'!A2,'Second Sheet'!$A$2:$A$20,0)),A2,"")

You can try this formula for unsorted random account numbers.

First Sheet:

first sheet.JPG

Second Sheet:

second sheet.JPG

Take it back I expanded your formula to cover all of the second sheet numbers and it works- thanks