I'm looking for the best way to compare data in 2 sheets. If data in column A matches on both sheets, I want to delete the entire row from the 2nd sheet.
The best way would be to have a temp column on sheet 2. In that column you can use a VLOOKUP like this
=IF(ISERROR(VLOOKUP(A1, SHeet1!A:A, 1, false)), 0, 1)
Thanks to rizvisa1 for this tip.
- Then apply filter on sheet 2
- Delete all the rows that are filtered
- Remove filter
- Clear out the temp column
- You can record these actions via excel macro recorder and it will give you a macro that you can use later too
This document, titled « Excel - A macro to compare and delete rows », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM