How to Count Cells that do not Contain Errors in Excel

This post will guide you how to count the number of cells that do not contain errors within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count the number of cells without any errors using a simple formula in Excel.

1. Count Number of Cells that do Not Contain Errors using Formula

You can use the SUMPRODUCT function in combining with NOT and ISERROR functions to count cells in a specified range of cells that do not contain errors

The below is a generic formula to count the number of cells that do not contain errors:

=SUMPRODUCT(--NOT(ISERR(range)))

Supposing that you wish to count the number of cells in range B1:B6 that do not contain errors, and you can use the following formula:

=SUMPRODUCT(--NOT(ISERR(B1:B6)))
count cell do not contain error1

LET’S SEE THAT HOW THIS FORMULA WORKS:

= NOT(ISERR(B1:B6))

count cell do not contain error2

The NOT and ISERROR functions can be used to identify the cells that do not contain an error. And it returns an array result with TRUE or FALSE values. The TRUE values indicate cells without errors, and FALSE values indicate cells with error values.

The array result is like this:

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}
count cell do not contain error3

= –NOT(ISERR(B1:B6))

count cell do not contain error4

Then you still need to convert the above array result to their numeric values, 1 and 0, so that the SUMPRODUCT function can calculate the sum of products of corresponding numbers. And you can do this with a double negative operator. And the final array result is like this:

{1;1;1;0;1;0}
count cell do not contain error5

The SUMPRODUCT function sums the items in the above array and return the final result.

You can also use the SUM function to count the cell that do not contain errors in range B1:B6. And the generic formula is similar with the above formula. It need to be entered as an array formula through pressing CTRL +SHIFT + Enter to convert it as array formula. The formula looks like this:

{=SUM(--NOT(ISERROR(B1:B6)))}
count cell do not contain error6

2. Count Cells that do not Contain Errors using User Defined Function with VBA Code

If you want to count the number of cells that do not contain errors in a range, you can write a user defined function with VBA code. Here are the steps to create and run the function:

Step1: Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

Step2: Click Insert > Module, and paste the following code in the Module Window.

How to Count Cells that do not Contain Errors in Excel vba1.png
Function CountNonErrors_ExcelHow(rng As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If Not IsError(cell.Value) Then
            count = count + 1
        End If
    Next cell
    CountNonErrors_ExcelHow = count
End Function

Step3: Save and close the window.

Step4: In a blank cell, enter the formula:

=CountNonErrors_ExcelHow(B1:B6)

Where B1:B6 is the range you want to count.

Step5: Press Enter to get the result. This function will return the number of cells that do not contain errors in the specified range.

How to Count Cells that do not Contain Errors in Excel vba2.png

3. Video: Count Cells that do not Contain Errors in Excel

This video will show you how to count cells that do not contain errors in Excel using a simple formula and a VBA code.

4. Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

How to Average and Ignore Errors in Excel

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average of a range of cells which might include some errors in Excel.

Average Cells Ignoring Errors


Assuming you want to calculate the average of the cells that excluding any errors in range B1:B6, and you can use the following formula based on the AVERAGEIF function, like this:

=AVERAGEIF(B1:B6,”>10”)

average and ignore errors1

Let’s See How This Formula Works:

The AVERAGEIF function can be used to calculate an average of some numeric values with one or more criteria. In the above example, the criteria is the expression of “>10”, and it means that you want to average all cell value in range B1:B6 which might include error values.

If you want to calculate the average of the cell values that ignoring any errors that might exist in the given range. And you can also use another array formula based on the AVERAGE function, the IF function and the ISERROR function. Like this:

=AVERAGE(IF(ISERROR(B1:B6),””,B1:B6))

Then you need to put this formula in a cell, and press Ctrl+Shift+Enter keys to make it as array formula. and you should see that the average result would be calculated.

average and ignore errors2

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

 

 

How to Count Number of Cells that Contain Errors in Excel

This post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells contain errors in your data range.

Count Number of Cells that Contain Errors


You should know that there are several types of errors such as: #N/A, #REF! or #VALUE! In Excel. And you only want to count the number of all types of errors with a formula in Excel. This tutorial will show you how to count the number of cells in a specified range of cell A1:B6 that contain errors using an Excel formula ,with the SUMRODUCT and ISERROR functions. Like below:

=SUMPRODUCT(–ISERROR(A1:B6))

count number cells that contain errors1

Note: A1:B6 is the range from which to count the cells that contain errors.

LET’S SEE THAT HOW THIS FORMULA WORKS:

The SUMPRODUCT function can be used to multiply the corresponding components in the given arrays. If there is only one array, SUMPRODUCT function would return the sum of values in the array.

=ISERROR(A1:B6)

count number cells that contain errors2

The ISERROR function is used to identify the cells that contain an error. And returns True if cell contain any error values. In this case, ISERROR function returns an array that contain TRUE or FALSE values, like this:

={FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE}

count number cells that contain errors3

=–ISERROR(A1:B6)

count number cells that contain errors4

You still need to convert the TRUE or FALSE values to number 1 and 0 by using a double negative operator. And the result array looks like this:

{0,0;0,0;0,1;1,0;0,0;0,1}

count number cells that contain errors5

The SUMPRODUCT function would add the value of the above array and return the final result.

You can also use another array formula based on the SUM function, IF function and ISERROR function to count the number of all types of errors in a range in Excel. Like this:

{=SUM(IF(ISERROR(A1:B6),1))}

count number cells that contain errors6

Note: you need to press CTRL + SHIFT + ENTER short keys to covert its as a array formula.

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • 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)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…

 

How to Compare Two Columns and Remove the Duplicate Values by Formula in Excel

To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If you are not familiar with ‘Conditional Formatting’ feature, you can also use a formula to compare columns and remove the duplicate values. In this article, we will introduce you how to apply IF, ISERROR and MATCH functions in a formula to compare data, I hope after reading this article, you will find the way to resolve your problem.

Precondition:

See screenshot below. We prepare two lists with fruits. As the two lists are simple, so we can see ‘Apple’ exists in both two columns obviously. But if the list is very long, we cannot compare them easily, we need to use formula to compare them.

How to Compare Two Columns and Remove the Duplicate Values by Formula 1

Method: Compare Two Columns and Remove Duplicates by Formula


Step 1: In B2 which is just between two columns, enter the formula =IF(ISERROR(MATCH(A2,$C$2:$C$7,0)),”Not Duplicate”,”Duplicate”).

How to Compare Two Columns and Remove the Duplicate Values by Formula 2

Step 2: Press Enter to get value. Verify that we get ‘Duplicate’ in B2. That means ‘Apple’ in list1 also exists in list2.

How to Compare Two Columns and Remove the Duplicate Values by Formula 3

Step 3: Drag the fill handle down till the end of the list.

How to Compare Two Columns and Remove the Duplicate Values by Formula 4

Step 4: Now we already find out duplicate values, if you want to remove the duplicate value from list1, you can click B1, then click Data->Filter under Sort & Filter group.

How to Compare Two Columns and Remove the Duplicate Values by Formula 5

The filter dropdown list is created.

How to Compare Two Columns and Remove the Duplicate Values by Formula 6

Step 5: Click the small arrow button to load criteria, check on ‘Duplicate’, then click OK.

How to Compare Two Columns and Remove the Duplicate Values by Formula 7

Duplicate value in list1 is displayed.

How to Compare Two Columns and Remove the Duplicate Values by Formula 8

Step 6: Just remove duplicate value from filtered list1. Now there is no duplicate value now.

How to Compare Two Columns and Remove the Duplicate Values by Formula 9


Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

How to Compare Two Columns to Find Missing Value (Unique Value) in Excel

Suppose we have two lists, list A contains all students, list B only contains students who passed exam. So, list A is longer than list B, and students in list B are all included in list A. If we want to find out which student failed the exam, we can though compare the two list and find out the missing value to confirm the name list. Actually, in our work we often meet the situations that we need to compare two lists and find out the missing values. In this tutorial, we will help you to find out missing values via two ways, the first one is by Conditional Formatting function in excel, the second one is by using formula with VLOOKUP function.

Precondition:

Prepare two lists. List A contains all students. List B contains partial of them.

Compare Two Columns 1

Method 1: Compare Two Columns to Find Missing Value by Conditional Formatting


Step 1: Select List A and List B.

Compare Two Columns 2

Step 2: Click Home in ribbon, click Conditional Formatting in Styles group.

Compare Two Columns 3

Step 3: In Conditional Formatting dropdown list, select Highlight Cells Rules->Duplicate Values.

Compare Two Columns 4

Step 4: In Duplicate Values dialog, select Unique in dropdown list. Keep default value in values with dropdown list. Then click OK.

Compare Two Columns 5

Step 5: Verify that unique values are marked with dark red properly.

Compare Two Columns 6

This way can be used in finding unique values from two lists.

Method 2: Compare Two Columns to Find Missing Value by Formula


Insert a new column between List A and List B.

Compare Two Columns 7

Step 1: In B2 enter the formula =ISERROR(VLOOKUP(A2,$C$2:$C$11,1,FALSE)). VLOOLIP function can help us to look up match value from List B. ISERROR function is used for checking whether match value exists or not, it returns TRUE or FALSE.

Compare Two Columns 8

Step 2: Drag the fill handle down. Verify that B4 and B10 are filled with TRUE.

Compare Two Columns 9

So, in List A, Calvin and Peter are students who failed in exam.

Step 3: If you want to show student name in new list directly, you can update formula =IF(ISERROR(VLOOKUP(A2,$C$2:$C$11,1,FALSE)),A2, “”).

Compare Two Columns 10

Step 4: After applying above formula for the following cells, missing value will display in new list directly.

Compare Two Columns 11

Video: Compare Two Columns to Find Missing Value (Unique Value) in Excel

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

How to Ignore Error Values When Calculating the Average in Excel

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!.

Assuming that you have a list of range of cells that you want to calculate the average of those cells which including error values , such as: #div/0 or #NAME?, etc. When you use the Average function to calculate those cells, you will get an error result. So How to handle this in Excel.

average cells ignore error1

If you want to ignoring any errors that might exist in the given range, you can use AVERAGEIF function or AVERAGE function with IF function to achieve the result. Like this:

=AVERAGEIF(A1:C4,”>=0”)

Or

=AVERAGE(IF(ISERROR(A1:C4),””,A1:C4))

OR

=AVERAGE(IF(ISNUMBER(A1:C4),A1:C4))

 

average cells ignore error2

average cells ignore error3

Note: The second and third formula need to press Ctrl + Shift +  Enter keys, as those formulas are Array Formula. A1:C4 is the data range that you wish to average. And you need to change it as you need.

Let’s see how the first formula works: the AVERAGEIF function can be used to calculate an average of numeric values with one or more criteria for a given range. And In this example, the criteria is the expression “>=0”, it will filter out all error values, and just to calculate the positive numeric values in the given range.

 

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

How to Compare Two Lists in Different Worksheets using VLOOKUP in Excel

This post will guide you how to compare two lists in different worksheets in Excel. How do I compare two lists in different worksheets with VLOOKUP function in Excel 2013/2016.

Comparing Two Lists in Different Worksheets


Assuming that you have two lists of data in the different worksheets, and you want to compare those two lists and try to check the values in the first list(Sheet2 – A1:A5) if it is in another list (Sheet6 – A1:A5). How to do it. You can use an Excel formula based on the VLOOKUP function, the IF function and the ISERROR function.

compare two list using vlookup

Here is the formula that comparing two lists in different worksheets:

= IF(ISERROR(VLOOKUP(A1,'Sheet6'!$A$1:$A$5,1,0)),"No","Yes")

You need to type this formula into cell B1 in the first worksheet that containing the first list data. And then drag the Fill Handle down to the cells to comparing other values with the second list in another worksheet.

compare two list using vlookup2

You would notice that the checking results have been returned in column B. if it returns Yes, it indicates that this cell value is also in the second list in another worksheet. If it return No, it indicates that this cell value is not in the second list.

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….

How to Sum Range Ignoring Errors in Excel

This post will guide you how to sum range of cells ignoring Errors in Excel. How do I sum and ignore errors with a formula in Excel. How to sum range ignoring all error values with VBA macro in Excel.

Sum Range Ignoring Errors with Formula


Assuming that you have a list of data in range A1:C4, in which contain numeric values and the error values, such as: #NAME?, #DIV/0, etc. and you want to sum this range but ignoring all error values. How to do it. You can use an Excel Array formula based on the SUM function, the IF function and the ISERROR function to achieve the result. Like this:

=SUM(IF(ISERROR(A1:C4),””,A1:C4))

You need to type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard to change this formula as array formula.

sum range ignoring errors1

Sum Range Ignoring Errors with VBA


You can also use an Excel VBA Macro to achieve the same result of summing a selected range of cells ignoring all error values. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

Sub SumRangeIgnoringError()

    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range:", "SumRangeIgnoringError", myRange.Address, Type:=8)
    For Each myCell In myRange
        If Not (IsError(myCell.Value)) Then
            lSum = lSum + myCell.Value
        End If
    Next
    MsgBox lSum, , "SumRangeIgnoringError"
End Sub

sum range ignoring errors2

#5 back to the current worksheet, then run the above excel macro. Click Run button.

sum range ignoring errors3

#6 Please select one range that contain error values. Click OK button.

sum range ignoring errors4

#7 Let’s see the result:

sum range ignoring errors5

Related Functions


  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

 

Check If Value in a Column exists in Another Column in Excel

This post will guide you how to check if one value in a column exists in another column in Excel. How do I check if value in column A exists in Column B with a formula in Excel. How to determine if values exists in another column in Excel.

Check If Value in a Column exists in Another Column


Assuming that you have a list of data in two columns in range A1:B5, and you want to check if one value in column A exists in column B, if True, then return true, otherwise, returns False. How to achieve it. You can create a new formula based on the IF function, the ISERROR function and the VLOOKUP function. Like this:

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$5, 1, FALSE)),FALSE,TRUE )

Type this formula into cell C1, and press Enter key in your keyboard, and then drag the autofill handle over other cells to apply this formula to check other cells in Column A.

check if value in another column1

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

Find Duplicate Values in Two Columns

This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so that the duplicate values can be highlighted or listed in a range of Cells.

Assuming that you need to compare two columns (Column A and Column B) to get the duplicate values in your worksheet, you can find duplicate values in two columns with Excel Formula, or Excel VBA Macro code.

Method 1: Find duplicate values in two columns with Excel Formula

To compare two given columns and find duplicate values in Excel, you can use a combination of the IF function, the ISERROR function, and the MATCH function to create an Excel formula. So you can use the following formula:

=IF(ISERROR(MATCH(A1,$B$1:$B$4,0))," ",A1)

Now you need to type this formula in Cell C1, press Enter key, drag AutoFill Handle down to Cell C2:C4, you will see all of the duplicated values are displayed in Column C.

find duplicate values in two columns1

Method 2: Find duplicate values in two columns with VBA Macro code

If you are familiar with the programming language and you can use a Visual Basic Macro to compare the value in two columns and then find duplicate values, just refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

find duplicate cells in two columns111

Sub FindUplicatesinTwoColumns()
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Select the first range in one column:", "FindUplicatesinTwoColumns", Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Select the second range in another column:", "FindUplicatesinTwoColumns", Type:=8)

    For Each R1 In Range1
        xValue = R1.Value
        For Each R2 In Range2
            If xValue = R2.Value Then
                If R3 Is Nothing Then
                    Set R3 = R1
                Else
                    Set R3 = Application.Union(R3, R1)
                End If
            End If
        Next
    Next
    R3.Interior.ColorIndex=3

End Sub

5# back to the current worksheet, then run the above excel macro.

find duplicate values in two columns3

6# Select the first range A1:A4 in Column A, click OK button, then select the second range B1:B4 in Column B, click OK button.

find duplicate values in two columns4 find duplicate values in two columns5

7# let’s see the result:

find duplicate values in two columns6

Method 3: Find duplicate values in two columns with Conditional Formatting feature

You can use conditional formatting with on a formula based on the COUNTIF function and the AND function to find the duplicate values in two specified columns and then highlighted them. Just do it following:

1# Select the entire Column A via click on the Column Header and then the column A will be highlighted

find duplicate values in two columns7

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

find duplicate values in two columns8

3# select Use a formula to determine which cells to format as Rule Type in the New Formatting Rule window

4# Type the following formula in the Format values where this formula is true: box

=COUNTIf($B:$B, $A1)

5# click the Format… button, then the Format Cells window will appear.

6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button.

7#you will be back to the New Formatting Rule windows and you can check a preview of the formatting you have selected. Then click OK button.

find duplicate values in two columns9

Then the conditional formatting rule will be applied to all values in two columns and highlighted the duplicate values.

find duplicate values in two columns9


Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • 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)…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

Related Posts

  • Highlight Rows
    You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
  • Find Duplicate Rows
    If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
  • Highlight Duplicate Rows
    this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
  • Highlight duplicate values
    this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……

Excel ISError Function

This post will guide you how to use Excel ISERROR function with syntax and examples in Microsoft excel.

Description

The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, such as: #N/A, #VALUE!,#REF!,#DIV0!, #NAME?, etc. and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A.

The ISERROR function is a build-in function in Microsoft Excel and it is categorized as an Information Function.

The ISERROR function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ISERROR function is as below:

= ISERROR (value)

Where the ISERROR function argument is:

  • Value -This is a required argument. The value that you want to test.

Example

The below examples will show you how to use Excel ISERROR Function to test if the value is any of error values. If so, return TRUE.

#1 using the following formula to check #N/A error.

= ISERROR (B1)

excel iserror function example1

#2 using the ISERROR function to check Cell B1 contains the value “excel”, enter the below formula in Cell C1 formula box:

=ISERROR(B1)

excel iserror function example1

As it is not an error type, so returns FALSE.


Related Functions

  • Excel ISNUMBER Function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:=ISNUMBER (value)
  • Excel ISERR Function
    The Excel ISERR function returns TRUE if the value is any error value except #N/A.The syntax of the ISERR function is as below:= ISERR (value)…
  • Excel ISBlank Function
    The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:=ISBLANK (value)…
  • Excel ISNA Function
    The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE. The syntax of the ISNA function is as below:=ISNA(value)…
  • Excel ISTEXT Function
    The Excel ISTEXT function used to check if a value is text. If so, returns TRUE; if the text is not text, the function will return FALSE. The syntax of the ISTEXT function is as below:=ISTEXT(value)…
  • Excel ISNONTEXT Function
    The Excel ISNONTEXT function used to check if a value is text. If so, returns FALSE; if the text is not text, the function will return TRUE. The syntax of the ISNONTEXT function is as below:=ISNONTEXT (value)…
  • Excel ISREF Function
    The Excel ISREF function used to check if a value is a valid reference. If so, returns TRUE; if the value is not a reference, the function will return FALSE. The syntax of the ISREF function is as below:=ISREF(value)…