Okay, so here is the scenario. There are two columns (maybe more) in Microsoft Excel. Let’s call those columns A and B. Those columns hold multiple values in the form of text or numbers. You now want to remove the values that you have in column B from column A, i.e. you are getting rid of the common values between the two columns. This is easily achievable in Microsoft Excel.
We came through this scenario while managing our email list. We had our entire email list in column A and column B contained a smaller list which wanted to unsubscribe or were bouncing. We definitely had to remove the emails in column B from column A and we discovered a way to do it through Microsoft Excel.
Here’s how you do it:
An example spreadsheet in Excel
The Full List column or column A contains our entire list. We have to remove the two emails contained in the Remove Column i.e. column B.
Now, since this is an example we’ve used only a few rows with values. In reality, this list can be filled with thousands of values. So, don’t think about manual removal. We’re going to use the Sort & Filter option along with Conditional Formatting for this.
First, we made the full list in column A contain only unique values. You can see that it contains [email protected] twice.
- So, we select column A.
- In the Data tab under the Sort & Filter group, click on Advanced.
- In Advanced Filter, check the Unique records only option and hit OK.
- It removes duplicate values from column A.
Now, we find what is common between column A and column B and highlight it.
- Select both column A and column B.
- In the Home tab, under the Styles group choose Conditional Formatting > Highlight Cell Rules > Duplicate Values.
- Click OK on the next dialog.
- It highlights all the duplicate or common values between the two columns.
Next, we sort column A by cell color.
- Highlight column A.
- In the Home tab, in the Editing group, click on Sort & Filter > Custom Sort.
- Select Continue with the current selection and hit Sort in the Sort Warning dialog.
- In the Sort dialog, under Sort On heading, choose the Cell Color option.
- Hit OK and all the highlighted cells will shift to the bottom.
- Select all the highlighted cells that are in the bottom and hit delete.
Using these steps, you have gotten rid of all the common values between two columns from the first one. It might seem overwhelming but all we’re doing is listing unique values, highlighting what is common, moving the highlighted columns to the bottom and deleting them manually.
We hope that the screenshots provided above along with the steps helped you out. Hoping that you carried out the steps smoothly. If you get stuck or have a better way then do not forget to post it in the comments.
No Comments