Sometimes, while updating data, we have to delete Excel rows from a list based on another list. Luckily, 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.
How to Delete Rows Based on Another List in Excel: 5 Methods
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 the 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 the 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.
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 functions 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}
However, 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}
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 details).
🔎 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}
4. Use IF and COUNTIF Functions to Delete Excel Rows Dependent on Another List
You can combine the COUNTIF function along with the IF function to remove 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 the 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).
{ }
Related Content: How to Delete Rows in Excel with Specific Text
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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.
Related Articles
- How to Delete All Rows Below a Certain Row in Excel?
- How to Remove Highlighted Rows in Excel?
- How to Delete Blank Rows in Excel?
- How to Delete Row If Cell Contains Specific Values in Excel?
- How to Delete Row If Cell Is Blank in Excel?
- How to Delete Empty Rows at the Bottom in Excel?
- How to Delete All Rows Not Containing Certain Text in Excel?
