- Start Excel.
- Press ALT+F11 to start the Visual Basic editor.
- On the Insert menu, click Module.
- Enter the following code in a module sheet:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub
- Press ALT+F11 to return to Excel.
- 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
- Select the range A1:A5.
- In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Macros.
- In Excel 2007, click the Developer tab, and then click Macro in the Code group.
- Click Find_Matches, and then click Run.
- The duplicate numbers are displayed in column B. The matching numbers will be put next to the first column, as illustrated here:
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