最新消息:点击查看大S的省钱秘笈

Easy way to Compare two out-of-order lists in MS Excel

电脑技巧 Slyar 60浏览 0评论

文章作者:姜南(Slyar) 文章来源:Slyar Home (www.slyar.com) 转载请注明,谢谢合作。

2015-01-26_103141

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)

2015-01-26_102641

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"

2015-01-26_102724

2015-01-26_102743

2015-01-26_102757

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"

2015-01-26_102813

2015-01-26_102820

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.

2015-01-26_102929

2015-01-26_103010

2015-01-26_103001

转载请注明:Slyar Home » Easy way to Compare two out-of-order lists in MS Excel

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址