Delete rows in Excel from a list

I had the need recently where I was provided an Excel XML file containing a large list of SKU’s. From this list I queried a SQL database to figure out which SKU’s had no onhand, sales or movement transactions for the past few years.

Once complete, I had a list of several hundred SKU’s that matched the above criteria. But the client wanted me to return to them the XML file in the same format as provided. Probably the quickest way would be to figure out how to take this list of SKU’s and delete the related records in the Excel sheet.

What I did was this:

• Create a new sheet (Sheet2) in excel and paste the list of SKU’s. For the purposes of this demonstration, let’s just say this list was 200 strong.
• Back in the main sheet (Sheet1) I added a new column beside the SKU column.SKU Column was (Column B).
  New Column was (Column C).
• Entered the following formula into the first row in Column C:

=COUNTIF(Sheet2!$A:$B,$B2)>0

What this does is return FALSE if the SKU is not found in the list. You can simply click and drag this cell to the very bottom to automatically apply to all SKU’s.

What’s left is a list of FALSE and TRUE. The TRUE records are the records I needed to delete from Excel.

What I did here was apply a Custom Sort,mainly because my row contained column headers.

• Click the Sort & Filter button in Excel.
• Select Custom Sort.
• Select Expand with selection. Click Sort.
• Choose the Column you’re sorting. In my case it was my new column, Column C.
• Check the option My data has headers.
• Click OK.

Now we have a sort where FALSE appears at the top, and TRUE appears at the bottom. I was quickly able to highlight the TRUE records, which matched 200, and delete them from Excel.

Saved an an Excel XML file and returned to the client.

Quick and dirty way of deleting multiple records in Excel from a list. I hope this helps you.