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

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext