A Simple Way to Scrub Column Data in Excel

Let’s pretend I’m a business manually keeping a list of customers in Excel for a win-back campaign. I need to unsubscribe people who have used their code to prevent them from getting another email. I don’t have a CRM system or a query written to scrub the data for me automatically. Each email gets a unique promo code to redeem 20% off their next purchase and I only want them to use it once. All I have is a list of emails, associated promo codes and redeemed codes. Here’s an easy way to compare codes and output a scrubbed list of emails to be unsubscribed from your campaign.

How it Works

By using an Excel IF statement I can tell the cell to compare the Associated Code to any of the codes in the Codes Used column. In the highlighted example below I’m telling Excel to look for the same B3 code (snake) anywhere in column D. If there is a match then display the email in column F. Super simple and a very easy way to scrub your list. Write it once and then copy and paste into the cells you want the statement. Excel will automatically update the associated cell values for you.

Breaking Down the IF Statement

Let’s breakdown the statement so you can change it to work for you. Below is the statement to copy and edit. I will be discussing the parts in bold.

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),””,A2)

B2 = The cell being checked for a duplicate entry in column D.

$D$2:$D$8 = This entry is what B2 will be checked against. The colon signifies a range. In this case check for duplicates from D2 through D8.
NOTE: If your sheet has 3000 rows the $8 needs to change to $3000 so the statement knows to check all rows for a match. To make my life easier I would make that number 10,000 to cover my needs for a while.

A2 = If a match then what data do you want to display in column F? In this case the persons email or the entry from column A.

Unsubscribing the Emails

Now that you have the emails who have used their promo code you can simply copy and paste them or import them into your email software to unsubscribe them.

I’m sure this is just one of many ways to scrub a list in Excel. I find this one simple and easy and it does the job.