You have two data ranges, D1 and D2, which are nearly identical. How to check what are the differences between the two ranges.

The first time I had this issue, I used COUNTIF function. Range and criteria are the arguments for COUNTIF. How many times criteria exists in the range.

In G3 I have COUNTIF, which calculates how many times does “8287” exist in D1 data range. If the value is zero, then the value is not found in D1. If the value is one, then the value exists once in D1.

In the same way, I have COUNTIF in B column. How many times does a value in D1 data. The sentence in B3 is =COUNTIF($F$3:$F$34;C3).

I added the header for the B column and filtered the values with zero COUNTIF. Values 1290 and 7008 exist in D1 range but not in D2.

Values 9002 and 3812 exist in data range D2 but not in D1.

We had some manual steps to find out differences between data ranges. One way to automate the process, is to use FILTER function.

FILTER is doing the same thing as I did with manual filtering. We take the values from C3:C34 where B3:B34 is zero and from F3:F34 where G3:G34 is zero.

We can have COUNTIF as embedded function inside the FILTER. COUNTIF function can be used with conditional formatting.

Select the first data range and take home – conditional formatting – manage rules.

Rule type is “Use a formula to determine which cells to format”.

Write the rule:

=countif($F$3:$F$34;C3)=0

Note that Excel defaults absolute reference, but criteria needs to be relative reference C3.

The results.

Repeat the process for the D2 data range, activate F3:F34.

Now the rule is =countif($C$3:$C$34;F3)=0 .

Highlighted values are not existing in another data ranges.

If you have the data in Access, let us use the following SQL query:

We select D1 values with left join, or outer join, to D2 with condition that D2 is empty. So, we join D1 to D2 but only when D2 is empty. Then we get values which are only in D1 but not in D2.

To have the other way, the values which exist only in D2 but not in D1, we need to have a mirror from previous query.

We are selecting D2 values from D2. We join D2 to D1 but the condition is that we want only D2 values when D1 is NULL.

I am sure that there are other ways to compare two data ranges to check which values exist only in one data range but not in another one. For me most practical way is to use conditional formatting. That does not populate any extra cells, only unique values are highlighted.