Sometimes, while updating data, we have to delete excel rows from a list based on another list. Luckily, there we can do this row deletion using excel functions. Besides, we can use **VBA** to remove rows dependent on another data list. So, let’s explore the methods to do the task.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 5 Methods to Delete Excel Rows Based on Another List

Suppose we have a dataset (**B5:C15**) containing several employees’ names and their residing states. Now some employees have left the company for some reason. We have listed those employees in a separate list (here, **E5:E8)**. Now from the main dataset, I will delete rows based on the list **E5:E8**; as these employees are no longer part of the company.

### 1. Delete Rows Based on Another List by Applying Excel COUNTIF Function and Sort Option

First, I will use **the COUNTIF function** to remove rows based on another list. In this method, I will add a helper column to the main dataset to specify which rows to delete. Then I will use the Sort option in excel to arrange the date to be deleted.

**Steps:**

- Type the below formula in
**Cell D5**(on helper column) at first.

`=COUNTIF($F$5:$F$8,B5)`

- Once you hit
**Enter**, the formula will return the below result. Next, use the**Fill Handle**(**+**) to copy the formula over the range**D6:D15**.

- As a result, the following will be the output. Here, the
**COUNTIF**function returns ‘**1**’ if any of the employee names from the range**B5:B15**matches the list**F5:F8**.

- Now, I will sort the data that matches to names to be deleted. To perform that, select the helper column and go to
**Data**>**Sort**.

- Later, the
**Sort Warning**dialog will appear, click**Sort**.

- As a consequence, the
**Sort**dialog will show up. Ensure the below fields are the same as the following screenshot and click**OK**.

- Upon clicking on
**OK**, all the matched rows will be sorted as below.

- Select all the rows that contain
**1**in the helper column by pressing the**Ctrl**key from the keyboard. Then right-click on the selection and press**Delete**.

- Finally, we will get the below result.

**Related Content:** **VBA to Delete Entire Row based on Cell Value in Excel (3 Methods)**

### 2. Apply Filter Option with Combination of IF, ISERROR, VLOOKUP Functions to Remove Rows Based on Another List

I will combine the **IF**, **ISERROR**, and **VLOOKUP** function to get the matched rows from a dataset that are matched to another list located in another excel sheet. Then I will delete matched rows using the **Filter** option in excel. To illustrate, my main dataset is located in **Sheet1** and the list of names to be deleted is in** Sheet2**.

**Steps:**

- Add an extra helper column to the main dataset (
**B4:C15**) and type the below formula in**Cell D5**(**Sheet1**) and press**Enter**.

`=IF(ISERROR(VLOOKUP(B5,Sheet2!B:B,1,FALSE)),"Keep","Delete")`

- You will get the below result. I have used the
**Fill Handle**to copy the formula to the rest of the cells. The formula used above put ‘**Delete**’ against employee names that matched to the list in**Sheet2**.

- Now I will filter all the rows that contain ‘
**Delete**’ from the helper column. To do that go to**Data**>**Filter**.

- As a result, the drop-down icon to apply the filter will appear. Click on the drop-down icon of the helper column and filter the data only for ‘
**Delete**’. Press**OK**.

- Once you press
**OK**, rows that contain ‘**Delete**’ will be filtered, Now, select all the rows and right-click on them. Then click**Delete Row**.

- Consequently, all the rows will be deleted. Press
**Ctrl + Shift + L**to withdraw the**Filter**. Ultimately, you will get the below result.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5,Sheet2!B:B,1,FALSE)**

Here the **VLOOKUP** function looks for names of **Cell B5 **(**Sheet1**) in column **B:B** (**Sheet2**) and return:

{**N/A**}

But, the formula returns the employee name if it is found in the list to be removed.

**ISERROR(VLOOKUP(B5,Sheet2!B:B,1,FALSE))**

Then, the **ISERROR **function converts the result of the **VLOOKUP** formula to **TRUE**/**FALSE**.. For **Cell D5**, this part of the formula returns:

{**TRUE**}

**IF(ISERROR(VLOOKUP(B5,Sheet2!B:B,1,FALSE)),”Keep”,”Delete”)**

Finally, the **IF** function returns **Keep **if the result of the** ISERROR** formula is **TRUE**, otherwise returns **Delete**. For** Cell D5**, the above formula returns:

{**Keep**}

**Read More: ** **How to Filter and Delete Rows with VBA in Excel (2 Methods)**

**Similar Readings:**

**How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)****Delete Unused Rows in Excel (8 Easy Ways)****Excel Shortcut to Delete Rows (With Bonus Techniques)****VBA to Delete Every Other Row in Excel (6 Criteria)****How to Delete Multiple Rows in Excel at Once (5 Methods)**

### 3. Combine Excel ISNA, MATCH & IF Functions to Remove Rows Based on Another List

Now I will use the combination of **ISNA**, **MATCH**, and** IF** functions to detect the rows that have matched data from another list. Likewise, the previous method, main dataset, and list to be deleted are located in different worksheets (**SheetX** and **SheetY**). I will add a helper column to the main dataset this time too.

**Steps:**

- Type the following formula in
**Cell D5**and hit**Enter**.

`=IF(ISNA(MATCH(B5,SheetY!B:B, 0)),"Keep", "Delete")`

- You will get the below result. Use the
**Fill Handle**to copy the formula to the range over**D6:D15**.

- Now you can apply
**SORT**or**FILTER**to the above result and thus remove the rows that contain ‘**Delete**’ in the helper column. (See**Method 1**or**Method 2**for detail).

**🔎**** How Does the Formula Work?**

**MATCH(B5,SheetY!B:B, 0)**

Here, the **MATCH **function matches the value in **Cell B5** (**SheetX**) in column **B:B** (**SheetY**) and returns the row number if the names are matched. Otherwise, it returns the **#N/A** error. The formula returns the following for **Cell D5**:

{**#N/A**}

**ISNA(MATCH(B5,SheetY!B:B, 0))**

Later **MATCH **formula is passed through the **ISNA** function to return **TRUE**/**FALSE** depending on the match/mismatch. For **Cell D5**, the formula returns:

{**TRUE**}

**IF(ISNA(MATCH(B5,SheetY!B:B, 0)),”Keep”, “Delete”)**

Finally, the** IF **function returns **Keep** if the result of the** ISNA** formula is **TRUE**, returns **FALSE **otherwise. The following is returned for **Cell D5**:

{**Keep**}

**Read More: Formula to Remove Blank Rows in Excel (5 Examples)**

### 4. Use IF and COUNTIF Functions to Delete Excel Rows Dependent on Another List

You can combine the **COUNTIF** function along with the **IF **fruition to remove excel rows that contain data from another list. Unlike previous methods, here I will use three excel worksheets to perform the task. Say your main dataset is in** SheetA**.

On the other hand, the list to be removed is in **SheetB**.

Let’s follow the below steps to complete the operation.

**Steps:**

- First, go to a new worksheet (
**SheetC**). Now type the below formula in**Cell B4**of**SheetC.**

`=IF(COUNTIF(SheetA!$B:$B,SheetB!B5)>0,"",SheetA!B5)`

- Upon entering the formula you will get the below result. Now drag down the ‘
**+**’ sign until you receive**0**in return.

- We can see that the 4 blank rows out of 12 rows. This is because these names in these
**4**rows match the list**B5:B8**of**SheetB**.

- Lastly, now you can delete all the blank rows from the above output in
**SheetC**by simply right-clicking on the blank rows.

**🔎**** How Does the Formula Work?**

**COUNTIF(SheetA!$B:$B,SheetB!B5)**

Here, the **COUNTIF** function looks for values of **Cell B5** (**SheetB**) in column **B:B** (**SheetA**) and returns the count. For the first entry of **Sheet C** the formula returns:

{**1**}

**IF(COUNTIF(SheetA!$B:$B,SheetB!B5)>0,””,SheetA!B5)**

Next, the **IF **function returns blank (**“ ”**) if the result of the **COUNTIF** formula is greater than **0**, otherwise the formula returns the employee name from **SheetA**. The above formula returns the below result for **Cell B5** (**SheetC**).

{ }

**Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)**

### 5. VBA to Delete Excel Rows Dependent on Another List

Till now, I have used excel functions to remove rows based on another list. Now I will apply a **VBA **code to remove the matched rows. This method is very easy and saves a lot of time. To perform the method, I have put my main dataset (**A1:A12**) in **Sheet7 **and the list to be removed (**A1:A5**) in **Shee8**.

**Steps**:

- Right-click on
**Sheet7**and click on**View Code**to bring up the**VBA**window.

- Then type the below code in the
**Module**and run the code using the**F5**key or pressing the**Run Sub**/**UserForm**icon (see the screenshot).

```
Sub DelRows_TwoLists()
Dim iList As Integer
Dim Ctr As Integer
Application.ScreenUpdating = False
iList = Sheets("sheet7").Range("A1:A12").Rows.Count
For Each x In Sheets("Sheet8").Range("A1:A5")
For Ctr = 1 To iList
If x.Value = Sheets("Sheet7").Cells(Ctr, 1).Value Then
Sheets("Sheet7").Cells(Ctr, 1).EntireRow.Delete xlShiftUp
Ctr = Ctr + 1
End If
Next Ctr
Next
Application.ScreenUpdating = True
MsgBox "Complete!"
End Sub
```

- As a result, rows matching the list of
**Sheet8**will be removed. The message box below will appear. In the end, click**OK**to end the process.

**Read More: How to Delete Row Using VBA (14 Ways)**

## Conclusion

In the above article, I have tried to discuss several suggestions to delete rows based on another list in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.