This post will explain various methods to exclude values from one column based on values in another column in Excel.
Often, while working with large datasets, we need to remove certain values from a column based on criteria from another column. we will cover three different approaches to achieve this: using the VLOOKUP function, the COUNTIF function, and VBA code.
Prepare a table recording the PASS and FAIL status for students. Now we need to exclude students who are listed in PASS column but also listed in FAIL.
Table of Contents
1. Exclude Values from One Column by VLOOKUP Function
Step1: Insert a new column between column B and column C. Then in C2 enter the formula
Step2: Click Enter. Verify that Ada is displayed in this cell.
Step3: Drag down the fill handle. Verify that C2:C11 are filled with returned values of VLOOKUP function.
Through the screenshot we can see that values need to be removed are displayed in column C.
Step4: Create a filter on C1. Select row 1, click Data in ribbon, select Filter.
Step5: Click arrow button in C1, then Sort & Filter dialog is displayed. Check on Select All option first (this operation will activate all options checked), then uncheck #N/A. Click OK.
Then values need to be excluded are filtered in column B.
Step6: Now you can highlight them or remove them per your request. If you want to highlight them, just select the range marked in red in above screenshot, and click Home->Fill Color button to highlight them.
If you want to remove them, just copy this column to another worksheet, and then filter text by color.
Then delete these filtered cells (press F5 to trigger Go To dialog, and then click Special button, check on Visible cells only option and click OK, all visible cells are selected, then you can delete them). Copy and paste updated column to column B again.
2. Exclude Values from One Column by COUNTIF Function
Steps are similar with VLOOKUP function.
Step1: Insert a new column between column B and column C. Then in C2 enter the formula:
COUNTIF function is used for counting how many times B2 appears in range $D$2:$D$6. If B2 value appears in the selected range, COUNTIF returns the times B2 appears, if B2 doesn’t appear, COUNTIF returns 0.
Step2: Drag down the fill handle till reaching the last cell in column C. Verify that C2:C11 are filled with returned value 1 or 0.
For values in column B, if 1 is displayed in its adjacent cell of column C, this value should be excluded.
Now you can follow step#4-#6 in method1 to exclude them.
3. Exclude Values from One Column Based on Another Column with VBA Code
If you want to exclude values from one column based on values in another column with VBA code, and return an array result, you can use the following steps:
Step1: Open the Excel workbook and press ALT + F11 to open the Visual Basic Editor.
Step2: Click on Insert > Module to add a new module to the workbook.
Step3: Paste the following code into the module. Close the Visual Basic Editor and return to the Excel workbook.
Sub ExcludeValues_excelhow() Dim rng1 As Range, rng2 As Range, destRange As Range Dim arr1 As Variant, arr2 As Variant, result() As Variant Dim i As Long, j As Long, k As Long, flag As Boolean ' Prompt for the first range of values Set rng1 = Application.InputBox("Select the first range of values to exclude from:", Type:=8) If rng1 Is Nothing Then Exit Sub ' Prompt for the second range of values Set rng2 = Application.InputBox("Select the range of values to exclude based on:", Type:=8) If rng2 Is Nothing Then Exit Sub ' Prompt for the destination cell Set destRange = Application.InputBox("Select the destination cell:", Type:=8) If destRange Is Nothing Then Exit Sub ' Convert the input ranges to arrays arr1 = rng1.Value arr2 = rng2.Value ' Exclude values based on the second range ReDim result(1 To UBound(arr1, 1), 1 To 1) For i = 1 To UBound(arr1, 1) flag = False For j = 1 To UBound(arr2, 1) If arr1(i, 1) = arr2(j, 1) Then flag = True Exit For End If Next j If Not flag Then k = k + 1 result(k, 1) = arr1(i, 1) End If Next i ' Resize the result array 'ReDim Preserve result(1 To k, 1 To 1) ' Write the result to the destination cell destRange.Resize(k, 1).Value = result End Sub
Step4: Press ALT + F8 to open the Macros dialog box. Select the “ExcludeValues_excelhow” macro and click on the “Run” button.
Step5: select the first range of values to exclude from.
Step6: select the range of values to exclude based on.
Step7: select the destination cell to place the result.
Step8: The macro will exclude the values from the first range based on the values in the second range and write the result to the destination cell.
Now you should have a clear understanding of how to implement each of these methods and choose the one that suits your needs best.
5. Related Functions
- Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
- Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…