Compare Ranges in Excel

calendar Sep 8, 2018 · 7 min read · VBA  ·
Share on: linkedin copy

In this blog post, we’ll explore various ways to compare two ranges in Excel. Whether you’re a beginner or advanced user, there are several methods to accomplish this task. We’ll start by examining an array function, then dive into a solution using VBA. Additionally, we’ll highlight Inquire, a powerful yet often overlooked feature in Excel.

Tested With Excel 2016

1. Compare ranges based on output values only

1.1 Array function to compare output values

The following array formula can be used to check if two ranges are identical (only by looking at the results, not the formulas):

1{=IF(MAX(IF(A2:B3<>D2:E3;1;0))=1;"FALSE";"TRUE")}

Remember array formulas are entered by typing the formula starting with = and submitting it by pressing Ctrl+Shift+Enter.

The formula works like this:

  1. Generate a binary array to identify differences: 1's for unequal, 0's for equal IF(A2:B3<>D2:E3,1,0)

  2. Read the binary array

    • All cells in both ranges are equal: MAX() = 0
    • Not all cells in both ranges are equal: MAX () = 1

  3. Translate the number to a conclusion (text)

    • If all cell’s are equal → Return: Equal
    • If one cell is different → Return: Unequal

Example 1: Two ranges with identical output values (but different formulas), returns True

Example 2: Two ranges where one cell’s output value is different, returns False

1.2 Array function to count differences in output values

If you want to report how many cells are different between both ranges, this can be realised with a small adjustment to the previous formula. Instead of taking the MAX() value of the array formula, you can take the SUM(). Since we want to return the result of the summation, te last IF-statement can be removed as well.

1{=SUM(IF(A2:B3<>D2:E3;1;0))}

2. Compare ranges based on formulas

2.1 Compare based on formulas by using FORMULATEXT() in array-functions

To compare ranges more strictly, on the formula-level, the following formulas can be used.

1{=IF(MAX(IF(IFNA(FORMULATEXT(A7:B8);A7:B8)<>IFNA(FORMULATEXT(D7:E8);D7:E8);1;0))=1;"FALSE";"TRUE")}
2{=SUM(IF(IFNA(FORMULATEXT(A7:B8);A7:B8)<>IFNA(FORMULATEXT(D7:E8);D7:E8);1;0))}

Example 1: Two ranges with identical output values and no formulas

Example 2: Two ranges with identical output values and formulas

Example 3: Two ranges with identical output values and different formulas

As you can see, these formulas are similar to the formulas of 1.1 and 1.2. However, two functions are added: FORMULATEXT() and IFNA(). The FORMULATEXT() function returns the formula of a cell instead of the output. The problem with FORMULATEXT() is that it returns #NA when a cell doesn't contain a formula. To fix this problem, the IFNA() function is added. This function checks if FORMULATEXT() returns #NA or not. If it returns #NA, the formula returns the output of the cell instead.

2.2 Convert the formulas to a string and use previous array-functions

There's also a 'dirty-fix' to compare the formulas using the formulas of 1.1 and 1.2. With search/replace the equation sign of formulas can be replaced. This way the formulas are converted to strings To do this, select both ranges and press Ctrl+H. Replace all = with #. Now you can use the compare functions as described in 1.1 and 1.2. Afterwards you can restore the functions by replacing all # with = .

Remark: Be careful if you use the # character in one of your formulas/cells. If you use a # character somewhere in the selected range, you can replace the = sign with multiple ### and vice versa.

3. Fix to detect differences between cell value of 0 and empty cell

All the previously discussed formula combinations share the same problem. For Excel a cell containing the value 0 is equal to an empty cell. In many cases this can be a problem. A quick fix is to replace all the values of the empty cells with a space (" ") in the arrays. This gives the following formula combinations if you want to compare ranges based on output values only :
Return True/False

1{=IF(MAX(IF(IF(A2:B3<>"";A2:B3;" ")<>IF(D2:E3<>"";D2:E3;" ");1;0))=1;"FALSE";"TRUE")}

Return number of differences

1{=SUM(IF(IF(A2:B3<>"";A2:B3;" ")<>IF(D2:E3<>"";D2:E3;" ");1;0)}

If you want to compare the formulas, use the following formula combinations:
Return True/False

1{=IF(MAX(IF(IF(A7:B8<>"";IFNA(FORMULATEXT(A7:B8);A7:B8);" ")<>IF(D7:E8<>"";IFNA(FORMULATEXT(D7:E8);D7:E8); " ");1;0))=1;"FALSE";"TRUE")}

Return number of differences

1{=SUM(IF(IF(A7:B8<>"";IFNA(FORMULATEXT(A7:B8);A7:B8);" ")<>IF(D7:E8<>"";IFNA(FORMULATEXT(D7:E8);D7:E8);" ");1;0)}

4. VBA to the rescue!

So the formula combinations quickly become complicated. Therefore writing an UDF in VBA for comparing ranges might be an interesting option. The following UDF can be used to easily check if two ranges are identical. The function has 2 configuration options.

  • formulas:
    • TRUE: compare by formulas in cells,
    • FALSE: compare by cell values
  • num_diff:
    • TRUE: show the number of differences,
    • FALSE: show EQUAL / NOT EQUAL
  1Public Function jho_compare_ranges(range1 As Range, _
  2                               range2 As Range, _
  3                               Optional formulas As Boolean = True, _
  4                               Optional num_diff As Boolean = True) _
  5                               As Variant
  6
  7' **
  8' * Function:          jho_compare_ranges
  9' * Description:       This function checks if all cell-values in two ranges are identical
 10' *                    (1) formulas=true: to compare the formulas in the cells (default)
 11' *                    (2) formulas=false: to compare the output values in the cells
 12' *
 13' *                    (1) numdiff=true: return the number of differences (default)
 14' *                    (2) numdiff=false: return equal or not equal
 15' *
 16' * Author:            Johan Hostens 
 17' * Created:           17 Aug 2018
 18' * Last Update:       27 Aug 2018 15:12 CEST (24h-clock)
 19'**
 20
 21' Convert Range To Array
 22Dim range1_array As Variant, range2_array As Variant
 23
 24If formulas = True Then
 25    range1_array = range1.Formula
 26    range2_array = range2.Formula
 27Else
 28    range1_array = range1.Value2
 29    range2_array = range2.Value2
 30End If
 31
 32' Determine Boundaries of Array
 33Dim range1_row_lbound As Long, range1_row_ubound As Long, _
 34    range1_col_lbound As Long, range1_col_ubound As Long
 35    
 36range1_row_lbound = LBound(range1_array, 1)
 37range1_row_ubound = UBound(range1_array, 1)
 38range1_col_lbound = LBound(range1_array, 2)
 39range1_col_ubound = UBound(range1_array, 2)
 40
 41Dim range2_row_lbound As Long, range2_row_ubound As Long, _
 42    range2_col_lbound As Long, range2_col_ubound As Long
 43    
 44range2_row_lbound = LBound(range2_array, 1)
 45range2_row_ubound = UBound(range2_array, 1)
 46range2_col_lbound = LBound(range2_array, 2)
 47range2_col_ubound = UBound(range2_array, 2)
 48
 49' Check if both ranges have the same dimensions
 50If range1_row_lbound <> range2_row_lbound Or _
 51   range1_row_ubound <> range2_row_ubound Or _
 52   range1_col_lbound <> range2_col_lbound Or _
 53   range1_col_ubound <> range2_col_ubound Then
 54   
 55   jho_compare_ranges = "#R/E"
 56   Exit Function
 57End If
 58
 59' Define indices for loops
 60Dim i As Long, j As Long
 61
 62' Replace empty with space in two ranges (if .value2 is used)
 63If formulas = False Then
 64    For i = range1_row_lbound To range1_row_ubound
 65        For j = range1_col_lbound To range1_col_ubound
 66            If range1_array(i, j) = "" Then
 67                range1_array(i, j) = " "
 68            End If
 69        
 70            If range2_array(i, j) = "" Then
 71                range2_array(i, j) = " "
 72            End If
 73        Next j
 74    Next i
 75End If
 76
 77' Make Binary Array
 78Dim binary_array As Variant
 79ReDim binary_array(range1_row_lbound To range1_row_ubound, _
 80                   range1_col_lbound To range1_col_ubound)
 81
 82' Return number of differences (if num_diff)
 83If num_diff = True Then
 84
 85    For i = range1_row_lbound To range1_row_ubound
 86       For j = range1_col_lbound To range1_col_ubound
 87        If range1_array(i, j) <> range2_array(i, j) Then
 88            binary_array(i, j) = 1
 89        Else
 90            binary_array(i, j) = 0
 91        End If
 92       Next j
 93    Next i
 94
 95    Dim binary_sum As Long
 96    binary_sum = Application.WorksheetFunction.Sum(binary_array)
 97    jho_compare_ranges = binary_sum
 98    Exit Function
 99
100' Return equal/not equal (if not num_diff)
101Else
102
103    For i = range1_row_lbound To range1_row_ubound
104        For j = range1_col_lbound To range1_col_ubound
105         If range1_array(i, j) <> range2_array(i, j) Then
106            jho_compare_ranges = "Not Equal"
107            Exit Function
108         End If
109        Next j
110    Next i
111    
112    jho_compare_ranges = "Equal"
113    Exit Function
114
115End If
116
117
118End Function

5. Compare entire Excel-files

MS Excel 2016 has a built-in tab for comparing Excel-files. It's called INQUIRE. You can enable it by going to the MS Excel options → Addins. It's very straight forward.

Example of INQUIRE