The basic idea is to use a formula that can compare the values in column A with the values in column B, and return a logical value (TRUE or FALSE) indicating whether the value in column A exists in column B or not. Then, based on the logical value, we can either keep or delete the value in column A.
There are different ways to write such a formula, but one common method is to use the COUNTIF function. The COUNTIF function can count the number of cells in a range that meet a certain criterion. For example, =COUNTIF (B:B,A1) will count how many cells in column B are equal to the value in cell A1. If the result is zero, it means that the value in A1 does not exist in column B. If the result is greater than zero, it means that the value in A1 exists in column B.
Procedures
To remove all values in column A, that exist in column B, using the COUNTIF function, we can follow these steps:
- Insert a new column C next to column A.
- In cell C1, enter the formula
=COUNTIF (B:B,A1). This will return the number of times the value in A1 appears in column B. - Drag the formula down to fill the rest of column C. This will compare each value in column A with the values in column B, and return the corresponding count.
- Filter column C by selecting any cell in column C, and clicking on the Data tab, then the Filter button. This will add a drop-down arrow to the header of column C.
- Click on the drop-down arrow, and uncheck the box for 0. This will filter out all the rows where the value in column A does not exist in column B, and only show the rows where the value in column A exists in column B.
- Select all the visible cells in column A, and press the Delete key. This will delete all the values in column A, that exist in column B.
- Clear the filter by clicking on the drop-down arrow again, and selecting Clear Filter. This will show all the rows again, but with the values in column A that exist in column B removed.
- Delete column C, as it is no longer needed.
Explanation
To understand how the formula works, let’s look at an example with some real numbers. Suppose we have the following data in columns A and B:
| Column A | Column B |
|---|---|
| 12 | 626 |
| 241 | 129 |
| 35 | 412 |
| 412 | 88 |
| 532 | 663 |
| 626 | 723 |
| 88 | 932 |
We want to remove all the values in column A, that exist in column B. To do this, we insert a new column C, and enter the formula =COUNTIF (B:B,A1) in cell C1. This formula will look at the value in cell A1, which is 12, and count how many times it appears in column B. In this case, the result is 0, because 12 does not exist in column B. We drag the formula down to fill the rest of column C, and we get the following table:
| Column A | Column B | Column C |
|---|---|---|
| 12 | 626 | 0 |
| 241 | 129 | 0 |
| 35 | 412 | 0 |
| 412 | 88 | 1 |
| 532 | 663 | 0 |
| 626 | 723 | 1 |
| 88 | 932 | 1 |
Now, column C shows us the number of times each value in column A appears in column B. We can see that the values 412, 626, and 88 exist in column B, because they have a count of 1 in column C. The rest of the values do not exist in column B, because they have a count of 0 in column C.
Next, we filter column C by unchecking the box for 0. This will hide all the rows where the value in column A does not exist in column B, and only show the rows where the value in column A exists in column B. The table will look like this:
| Column A | Column B | Column C |
|---|---|---|
| 412 | 88 | 1 |
| 626 | 723 | 1 |
| 88 | 932 | 1 |
Now, we select all the visible cells in column A, and press the Delete key. This will delete the values 412, 626, and 88 from column A, because they exist in column B. The table will look like this:
| Column A | Column B | Column C |
|---|---|---|
| 88 | 1 | |
| 723 | 1 | |
| 932 | 1 |
Finally, we clear the filter by selecting Clear Filter, and delete column C, as it is no longer needed. The table will look like this:
| Column A | Column B |
|---|---|
| 12 | 626 |
| 241 | 129 |
| 35 | 412 |
| 88 | |
| 532 | 663 |
| 723 | |
| 932 |
We have successfully removed all the values in column A, that exist in column B, using the COUNTIF function.
Other Approaches.
- The
VLOOKUPfunction can look up a value in the first column of a table, and return a corresponding value from another column of the table. For example,=VLOOKUP (A1,B:B,1,0)will look up the value in A1 in the first column of the table B:B, and return the same value from the first column of the table B:B, or an error if not found. We can use this function to compare the values in column A with the values in column B, and return a logical value (TRUE or FALSE) indicating whether the value in column A exists in column B or not. For example,=ISERROR (VLOOKUP (A1,B:B,1,0))will return TRUE if the value in A1 does not exist in column B, and FALSE otherwise. We can use this formula in a new column, and filter by FALSE to delete the values in column A that exist in column B. - The
Remove Duplicatesfeature can remove duplicate values from a range of cells. We can use this feature to remove the values in column A that exist in column B, by copying column B to the end of column A, and then applying theRemove Duplicatesfeature to column A. This will delete all the duplicate values in column A, including the ones that came from column B. Then, we can delete the extra values from column B that we copied to column A.
These are some of the alternative ways to remove all values in column A, that exist in column B, in excel formula.
