How to Create Formulas Comparing Lists for Duplicates – MS Excel

How to Create Formulas Comparing Lists for Duplicates – MS Excel

Summary: How to Create Formulas Comparing Lists for Duplicates to see if there is duplicate information. Works with Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

1.Make sure there is a blank column just to the left of each list of part numbers on each worksheet.

2.Select the part numbers on the first worksheet and give them a name such as “List1″. (In Excel 2007 display the Formulas tab of the ribbon and then click on Define Name in the Defined Names group. In older versions of Excel use Insert | Name | Define.)

3.Select the part numbers on the second worksheet and give them a name such as “List2″.

4.Assuming that the first part number on the first worksheet is in cell A2, enter the following formula in cell A2:

=ISNUMBER(MATCH(A2,List2,0))

5.Copy the formula down so that a copy appears to the right of each part number on the first worksheet.
6.Repeat steps 4 and 5 on the second worksheet, but use the following formula:

=ISNUMBER(MATCH(A2,List1,0))

When you are done, either TRUE or FALSE will appear to the right of each part number on each worksheet. If TRUE appears, the associated part number appears on the other worksheet. If FALSE appears, then the part number is unique and does not appear on the other worksheet.