# 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".
When comparing cells, numbers formatted as text is always greater than the equivalent number.

### 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 `