Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Cells & Ranges > Comparing Data< Previous | Next > 
 

Comparing Two Ranges

 
 

The quickest way to compare two ranges of data is to use Conditional Formatting.

 
 

Depending on exactly what you want to compare will depend on which formula you use.

 
 

  • =NOT(B3=F3) - will compare the contents of every cell relative to its position in the table.

    		 
     

  • =COUNTIF(F3:H14,B3)=0 - will compare occurrences in each column.

    		 
     

  • =User Defined Function - will compare whole rows of data in a table.

    		 
     

    To make the formulas a little easier to understand select the first range of data, in this case "B3:D14" and assign it the named range "Table1".

    		 
     

    Do the same for the second range of data, in this case "F3:H14" and assign it the named range "Table2".

    		 

     

    Comparing Every Relative Cell

    		 
     

    Conditional formatting can be used to compare the contents of every cell in two tables.

    		 
       
     

    Select cells "B3:D14" and select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

    		 
     

    Select the "Formula is" in the first drop-down list and enter the formula "=NOT(B3=F3)".

    		 
     

    Click the Format button to apply your specific formatting, in this case we are just applying a red background.

    		 
       
     

    You can quickly get the total number of cells that are different by using the following Array Formula.

    		 
     

    This formula must be entered by pressing (Ctrl + Shift + Enter).

    		 
       

     

    Comparing Occurrences

    		 
     

    Conditional formatting can be used in conjunction with the COUNTIF() function to identify items that do not occur in their respective columns.

    		 
     

    Notice that none of the cells in the middle column are shaded since all these items occur in both tables.

    		 
     

    Notice also that the number 78 in cell "D5" is not shaded, this is because there is an occurrence in cell "H10".

    		 
     

    Notice also that the number 78 in cell "H10" is not shaded, this is because there is an occurrence in cell "D5".

    		 
       
     

    Select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

    		 
     

    Select the "Formula is" in the first drop-down list and enter the formula "=COUNTIF(Table2,B3)=0".

    		 
     

    Click the Format button to apply your specific formatting, in this case we are just applying a red background.

    		 
       
     

    Select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

    		 
     

    Select the "Formula is" in the first drop-down list and enter the formula "=COUNTIF(Table1,F3)=0".

    		 
     

    Click the Format button to apply your specific formatting, in this case we are just applying a red background.

    		 
       
     

    The cell reference in the COUNTIF function should always be the upper left cell of the current selection.

    		 

     

    Comparing Rows

    		 
     

    Conditional formatting can be used in conjunction with a user defined function to identify which rows appear in a table.

    		 
       
     

    Select cells "B3:D14" and select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

    		 
     

    Select the "Formula is" in the first drop-down list and enter the formula "=matchingrow($B3:$D3,$F$3:$H$14)".

    		 
     

    This formula will highlight all the rows that do occur in the table.

    		 
     

    If you want to highlight the rows that do not appear then you can change the formula to "=matchingrow($B3:$D3,$F$3:$H$14)=0".

    		 
     

    Click the Format button to apply your specific formatting, in this case we are just applying a red background.

    		 
       
     

    The function defined below allows you to pass in a single row of cells and a table of cells.

    		 
     

    The value returned will be the row number of the first occurrence or zero if the row does not exist.

    		 
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    Public Function MatchingRow(ByVal rgeRowToMatch As Range, _
                                ByVal rgeTableOfData As Range) As Long
    Dim lcheckrowno As Long
    Dim lcurrentrowno As Long
       Call Application.Volatile(True)
       For lcheckrowno = rgeTableOfData.Row To (rgeTableOfData.Row + rgeTableOfData.Rows.Count - 1)
          lcurrentrowno = lcheckrowno - rgeTableOfData.Row
          If rgeRowToMatch.Cells(1).Value = rgeTableOfData.Cells(1).Offset(lcurrentrowno, 0).Value Then
             If ColumnsMatching(rgeRowToMatch, rgeTableOfData, lcurrentrowno, 2, rgeRowToMatch.Cells.Count) = True Then
                MatchingRow = lcheckrowno
                Exit Function
             End If
          End If
       Next lcheckrowno
    End Function

    Public Function ColumnsMatching(ByVal rgeRowToMatch As Range, _
                                    ByVal rgeTableOfData As Range, _
                                    ByVal lRowNo As Long, _
                                    ByVal iColNo As Integer, _
                                    ByVal iNoOfColumns As Integer) As Boolean
    Dim icolumnno As Integer
    Dim iallmatch As Integer
       iallmatch = 0
       For icolumnno = iColNo To iNoOfColumns
          If rgeRowToMatch.Cells(icolumnno).Value <> rgeTableOfData.Cells(1).Offset(lRowNo, icolumnno - 1).Value Then
             iallmatch = iallmatch + 1
          End If
       Next icolumnno
       If iallmatch = 0 Then ColumnsMatching = True
       If iallmatch > 0 Then ColumnsMatching = False
    End Function
    		   

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >