Thread: Compare 2 columns and mark the duplicates

I was wondering if there is anyway I could do something similar to Cells.Find(What:=Paste) in a macro. I want to compare two columns of cells, one on the first worksheet and the other on the second worksheet, and identify which one from the first sheet are in the second one.

I tried recording a macro that would:
1) take the first cell from the first column
2) copy it (Ctrl+C)
3) go to the second sheet
4) paste it (Ctrl+V) in the "find" dialog box .. ***
5) turn on relative references and press tabs to select the cell right next to the "found" cell
6) type an "X"... or anything i'd like.. Shipped, received, missing blabla
7) go back to the first sheet
8) with relative references still turned on go down one cell
9)and then loop the macro from step 2 till it reaches the last filled cell from the first column in the first sheet.

***That's what bugging me, Excel won't record "Ctrl+V" in the macro, so it would keep looking for the values it first pasted.. let's say that when I recorded the macro i "Ctrl+C" the first cell which contains "1", it would loop but instead of "Ctrl+V" the newly "Ctrl+C" value, it would keep looking for the "1"...

So it didn't work..
I'm still new with macros so google is my best pal at the momment but it seems google has its limits.

Before Macro
Sheet 1

2
3

Sheet 2

1
2
3
4

After Macro
Sheet 1

2
3

Sheet 2

1
2 x
3 x
4

That's pretty much what i'd like to do.

So if anybody knows of a better way to achieve such a thing, I'd be more than pleased to hear it.

Hello powpowninjastar,

Welcome to the Forum!

Your title does not accurately reflect your question. Please change it. Thread titles should be in a format suitable to database searches. I would suggest "Compare 2 columns and mark the duplicates"

Ok, title changed

Hello powpowninjastar,

Thank you for changing the title. You can change the sheet names and the starting cells in the code to match what you will be using. Copy this code to a standard VBA module in your workbook.

Code:

Sub CompareColumns()

  Dim Cell As Range
  Dim DSO As Object
  Dim Key As String
  Dim Item As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  
    Set DSO = CreateObject("Scripting.Dictionary")
    DSO.CompareMode = vbTextCompare
    
    Set Sht1 = Worksheets("Sheet1")
    Set Sht2 = Worksheets("Sheet2")
    
      Set Rng = Sht1.Range("A1")
      Set RngEnd = Sht1.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Sht1.Range(Rng, RngEnd))
      
      For Each Cell In Rng
        Key = Trim(Cell.Text)
        If Key <> "" Then
          If Not DSO.Exists(Key) Then DSO.Add Key, 0
        End If
      Next Cell
      
      Set Rng = Sht2.Range("A1")
      Set RngEnd = Sht2.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Sht2.Range(Rng, RngEnd))
      
      For Each Cell In Rng
        Key = Trim(Cell.Text)
        If Key <> "" Then
          If DSO.Exists(Key) Then Cell.Offset(0, 1) = "x"
        End If
      Next Cell
      
    Set DSO = Nothing
    
End Sub