How to compare data in two columns to find duplicates in Excel

http://support.microsoft.com/kb/213367 

Method 1: Use a worksheet formula

To use a worksheet formula to compare the data in two columns,   follow these steps:  

  1. Start Excel.
  2. In a new worksheet, enter the following data (leave column B empty):
    A1: 1   B1:     C1: 3
    A2: 2   B2:     C2: 5
    A3: 3   B3:     C3: 8
    A4: 4   B4:     C4: 2
    A5: 5   B5:     C5: 0
    					
  3. Type the following formula in cell B1:
    =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),””,A1)
  4. Select cells B1:B5.
  5. In Microsoft Office Excel 2003 and in earlier versions of Excel, point to Fill on the Edit menu, and then click Down.
    In Microsoft Office Excel 2007, click Fill in the Editing group, and then click Down.
    The duplicate numbers are displayed in column B, as in the following example:
       A1: 1   B1:     C1: 3
       A2: 2   B2: 2   C2: 5
       A3: 3   B3: 3   C3: 8
       A4: 4   B4:     C4: 2
       A5: 5   B5: 5   C5: 0
    					
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s