How to compare two columns and find differences in Excel?

by

I have two columns in Excel that I want to compare and find the differences between them.

Suppose:

  • Col A has 50 numbers, i.e. 0511234567
  • Col B has 100 numbers in the same format

Answer

Using Conditional Formatting

Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:

=countif($B:$B, $A1)

Click the Format button and change the Font color to something you like.

Repeat the same for column B, except use this formula and try another font color.

=countif($A:$A, $B1)

Using a Separate Column

In column C, enter the ff. formula into the first cell and then copy it down.

=if(countif($B:$B, $A1)<>0, "-", "Not in B")

In column D, enter the ff. formula into the first cell and then copy it down.

=if(countif($A:$A, $B1)<>0, "-", "Not in A")

Both of these should help you visualize which items are missing from the other column.

Attribution
Source : Link , Question Author : Sundhas , Answer Author : Excellll

Leave a Comment