How to Exclude Values from One Column Based on Values in Another Column in Excel

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.

Exclude Values from One Column 1.png

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

=VLOOKUP(B2,$D$2:$D$6,1,FALSE)
Exclude Values from One Column 2.png

Step2: Click Enter. Verify that Ada is displayed in this cell.

Exclude Values from One Column 3.png

Step3: Drag down the fill handle. Verify that C2:C11 are filled with returned values of VLOOKUP function.

Exclude Values from One Column 4.png

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.

Exclude Values from One Column 5.png

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.

Exclude Values from One Column 6.png

Then values need to be excluded are filtered in column B.

Exclude Values from One Column 7.png

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.

Exclude Values from One Column 8.png

If you want to remove them, just copy this column to another worksheet, and then filter text by color.

Exclude Values from One Column 9.png

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($D$2:$D$6,B2)

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.

Exclude Values from One Column 10.png

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.

Exclude Values from One Column 11.png

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.

vba to Exclude Values from One Column 1.png
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.

vba to Exclude Values from One Column 2.png

Step5: select the first range of values to exclude from.

vba to Exclude Values from One Column 3.png

Step6: select the range of values to exclude based on.

vba to Exclude Values from One Column 4.png

Step7: select the destination cell to place the result.

vba to Exclude Values from One Column 5.png

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.

vba to Exclude Values from One Column 6.png

4. Conclusion

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)…