Compare 2 columns and delete duplicate

Hi all

I've searched but haven't found an answer that works yet.

I have data in column A as my master list. Column B is made up of data that exists in column A somewhere. If there a formula that will compare column B against column A and delete the row in column A if found in column B?

A formula can't be used to delete a row - the way to achieve that is to apply a formula which identifies the rows (e.g. in column C), and then you could apply a filter to that column and delete all the rows in one operation (or, you could use a macro). Put this formula in C2 (assuming your data starts in A2 - if it doesn't, insert a new row 1 at the top of your sheet and use it for header labels):

=IF(ISNUMBER(MATCH(A2,B:B,0)),"Delete","ok")

Then copy this down to the bottom of your data, and then apply autofilter to column C, select "Delete" from the drop-down, select all the visible cells, then click on Delete, then select All in the filter drop-down. Save your file with a different name, so that you can still get back to the original lists if you need to.

Hope this helps.

Pete

Thanks Pete! This worked perfectly. With column A ringing in 50,000+ cells, it took a while but worked great