Comparing


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.

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

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext