A typical task that periodically arises before every Excel user is to compare two ranges with data and find differences between them. The solution method, in this case, is determined by the type of initial data.
Option 1. Synchronous Lists
If the lists are synchronized (sorted), then everything is done very simply, because it is necessary, in fact, to compare the values in adjacent cells of each row. As the simplest option, we use a formula for comparing values, which produces boolean values at the output TRUE (TRUE) or LYING (FALSE):
The number of mismatches can be calculated by the formula:
=SUMPRODUCT(—(A2:A20<>B2:B20))
or in English =SUMPRODUCT(—(A2:A20<>B2:B20))
If the result is zero, the lists are identical. Otherwise, they have differences. The formula must be entered as an array formula, i.e. after entering the formula in the cell, do not press Enter, And Ctrl + Shift + Enter.
If you need to do something with different cells, then another quick method will do: select both columns and press the key F5, then in the opened window the button Highlight (Special) — Line differences (Row differences). In the latest versions of Excel 2007/2010, you can also use the button Find and select (Find & Select) – Selecting a group of cells (Go to Special) tab Home (Home)
Excel will highlight cells that differ in content (by row). They can then be processed, for example:
- fill with color or somehow visually format
- clear with key Delete
- fill everything at once with the same value by entering it and pressing Ctrl + Enter
- delete all rows with selected cells using the command Home — Delete — Delete rows from sheet (Home — Delete — Delete Rows)
- etc.
Option 2: Shuffled Lists
If the lists are of different sizes and not sorted (the elements are in a different order), then you have to go the other way.
The simplest and fastest solution is to enable color highlighting of differences using conditional formatting. Select both ranges with data and select on the tab home – Conditional formatting – Highlight cell rules – Duplicate Values:
If you select the option Recurring, then Excel will highlight the matches in our lists if the option Уникальные – differences.
Color highlighting, however, is not always convenient, especially for large tables. Also, if elements can be repeated inside the lists themselves, then this method will not work.
Alternatively, you can use the function COUNTIF (COUNTIF) from category Statistical, which counts how many times each element from the second list occurs in the first one:
The resulting zero indicates the differences.
And, finally, “aerobatics” – you can display the differences in a separate list. To do this, you will have to use an array formula:
Looks scary, but does the job perfectly 😉
- Highlight duplicates in the list with color
- Comparing two ranges with the PLEX add-on
- Prohibition of entering duplicate values