How do I achieve that? Any help would be much appreciated.
Given sheet 2:
ColumnA
-------
apple
orange
You can flag the rows in sheet 1 where a value exists in sheet 2:
ColumnA ColumnB
------- --------------
pear =IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Keep","Delete")
apple =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"Keep","Delete")
cherry =IF(ISERROR(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),"Keep","Delete")
orange =IF(ISERROR(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),"Keep","Delete")
plum =IF(ISERROR(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),"Keep","Delete")
The resulting data looks like this:
ColumnA ColumnB
------- --------------
pear Keep
apple Delete
cherry Keep
orange Delete
plum Keep
You can then easily filter or sort sheet 1 and delete the rows flagged with 'Delete'.