文章作者：姜南(Slyar) 文章来源：Slyar Home (www.slyar.com) 转载请注明，谢谢合作。
Here is an easy way to compare and highlight the differences of two ordered or out-of-order lists in MS Excel.
We will use COUNTIF function to accomplish the task.
The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.
Syntax
COUNTIF(range, criteria)
range: One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria: A number, expression, cell reference, or text string that defines which cells will be counted.
1. Suppose we got two lists. (The example is ordered lists, however unordered lists are also OK)
2. Select the range A1:A25 and B1:B25, right-click the mouse, choose "define name" and give them a name "left"; then using the same way to name range D1:D25 and E1:E25 as "right"
3. Select the range A1:A25 and B1:B25 again, then click the "Conditional Formatting" button under the "Home" tab, and then click "New Rule"
4. Select "Use a formula to determine which cells to format".
5. Enter the formula "=COUNTIF(right,A1)=0", it counts the number of teams in "right" that are equal to the team in cell A1. If COUNTIF(right,A1) = 0, the item in cell A1 is not in the "right" list. As a result, Excel fills the cell with a blue background color. And this operation will be copied to all selected cells before.
6. Select a formatting style (like background), and click OK.
7. To highlight the items in the right list that are not in the left list, select the range D1:D25 and E1:E25, create a new rule using the formula "=COUNTIF(left,D1)=0", and set the formatting style.
转载请注明：Slyar Home » Easy way to Compare two out-of-order lists in MS Excel