Compare lists for common values in Excel

Comparing lists for duplicates is a task that often has many
variables. You can compare values in the same list or you might want to compare
one list to another. Then there’s the definition of duplicate you need to
evaluate. How many values are necessary to satisfy the term? You’ll find many
solutions if you search the Internet, but you’ll find no one-size-fits-all
solution. You must know your data and apply an appropriate solution.

Built-in conditional formatting

In 10
cool ways to use Excel’s conditional formatting feature, #5 and #6 offer
conditional formatting rules for catching duplicate values. #5 compares values
in the same column. #6 finds discrepancies between values in two columns (or
lists). Now, suppose you want to highlight values that occur in both columns.
Neither of these solutions will work, but they’re close.

If you’re using a ribbon version, the solution is simple:
use one of the new built-in rules. To create the results shown in the images
below, do the following:

1. Select the dataset, A1:
   B8.
2. On the Home tab, click
   Conditional Formatting in the Styles group.
3. Choose Highlight Cells
   Rules and then select Duplicates Values in the subsequent menu.
4. In the resulting dialog,
   select an appropriate format – I accepted the default – and click OK.

This built-in rule highlights all duplicates across the
selected columns. It highlights duplicates in the same column and can handle
more than two columns.

Formula conditional formatting

If you’re using Excel 2003, or if the above rule provides
too much coverage, you can use a formula rule to get almost the same results, as shown in the image below.

To achieve the similar results shown in the image below, do
the following:

1. Select the first column,
   D1:D8.
2. On the Home tab, click
   Conditional Formatting in the Styles group.
3. Choose New Rule.
4. In the resulting dialog,
   select the last option in the first pane: Use A Formula To Determine Which
   Cells To Format.
5. Enter the formula
   =COUNTIF($E$1:$E$8,D1) and then click Format.
6. Select the appropriate
   options, and click OK.
7. Click OK to return to the
   sheet.

Repeat the steps above, selecting the data in column E,
E1:E8, and entering the formula =COUNTIF($D$1:$D$8,E1). (Don’t include the
period at the end; it’s grammatical.)

Applying this type of conditional format can have you
banging your head against the wall – so much can go wrong! In this example, the
function’s first argument is the same as the selected range. This arrangement
says: if a value in the selected range equals any value in the other referenced
column, highlight it.

This formula doesn’t catch duplicates in the same column.
You’d need an additional rule to cover those. In addition, this particular rule
restricts you to two columns.

Also read:

• How
  to find duplicates in Excel
• Use
  formulas to find multi-column duplicates in Excel
• 10
  advanced formatting tricks for Excel users