title: Find duplicates in two columns in two sheets
body: <p>I need to compare two columns in two different sheets and find out the duplicates: compare column C in 'Sheet2' to column E in 'Sheet1'. As a sample I have used only a few but:</p>
  
  <pre><code>Column E in Sheet1 has 2,000 rows of data.  
  Column C in Sheet2 has ~ 100 rows of data.
  </code></pre>
  
  <p>Screenshots of sheets: <a href="http://postimg.org/image/jtuinkqgz/" rel="nofollow noreferrer">http://postimg.org/image/jtuinkqgz/</a>  </p>
  
  <p>Sheet1<br>
  <a href="https://i.stack.imgur.com/jhFNe.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jhFNe.jpg" alt="#1 - Actually via Dropbox"></a>  </p>
  
  <p>Sheeet2<br>
  <a href="https://i.stack.imgur.com/KdvKx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KdvKx.jpg" alt="#2 - Actually via Dropbox"></a>  </p>
  
  <p>Link to <a href="http://www.filedropper.com/dropbox" rel="nofollow noreferrer">sample Excel file</a>.</p>
  
  <pre><code>Option Explicit
  Sub CompareColumns()
  '---------------------------------------------------------------------------------------------------
  This module loops through two columns in Excel and identifies items without a match. The columns can be on different sheets. -----------
  '---------------------------------------------------------------------------------------------------
  Dim strCol1 As String 'First Column Location
  Dim strCol2 As String 'Second Column Location
  Dim strColResults As String 'Output Column
  Dim strSheetname1 As String 'First sheet name
  Dim strSheetname2 As String 'Second sheet name
  Dim iListStart As Integer 'Row where List Begins
  Dim strTemp As String
  Dim i As Integer, j As Integer
  Dim iLastRow1 As Integer, iLastRow2 As Integer
  
  '---Edit these variables---'
  strSheetname1 = "Sheet1"
  strSheetname2 = "Sheet2"
  strCol1 = "A"
  strCol2 = "C"
  'strColResults = "B"
  iListStart = 1
  '--------------------------'
  
  iLastRow1 = Sheets(strSheetname1).Range(strCol1 &amp; "50000").End(xlUp).Row
  iLastRow2 = Sheets(strSheetname2).Range(strCol2 &amp; "50000").End(xlUp).Row
  
  'error check
  If iListStart &gt; WorksheetFunction.Min(iLastRow1, iLastRow2) Then
  MsgBox ("List not found. Perform logic check on input variables.")
  Exit Sub
  End If
  
  Sheets(strSheetname1).Range(strCol1 &amp; iListStart &amp; ":" &amp; strCol1 &amp; iLastRow1).Interior.ColorIndex = 0
  Sheets(strSheetname2).Range(strCol2 &amp; iListStart &amp; ":" &amp; strCol2 &amp; iLastRow2).Interior.ColorIndex = 0
  
  strTemp = "&lt;&lt;"
  If iLastRow2 &gt; iLastRow1 Then 'switch the order
  strTemp = strCol1
  strCol1 = strCol2
  strCol2 = strTemp
  strTemp = strSheetname1
  strSheetname1 = strSheetname2
  strSheetname2 = strTemp
  strTemp = "&gt;&gt;"
  End If
  
  'Identify unmatched items in long column
  For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
  If UCase(Sheets(strSheetname2).Range(strCol2 &amp; j)) =          UCase(Sheets(strSheetname1).Range(strCol1 &amp; i)) Then
  'Range(strColResults &amp; i) = i &amp; " to " &amp; j
  Exit For ' Stops at first match
  ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
  'Range(strColResults &amp; i) = strTemp
  Sheets(strSheetname1).Range(strCol1 &amp; i).Interior.Color = 255
  End If
  Next j
  Next i
  
  'Identify unmatched items in short column
  If strTemp = "&lt;&lt;" Then
  strTemp = " &gt;&gt;"
  Else
  strTemp = " &lt;&lt;"
  End If
  
  For i = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
  If UCase(Sheets(strSheetname1).Range(strCol1 &amp; j)) =             UCase(Sheets(strSheetname2).Range(strCol2 &amp; i)) Then
  Exit For
  ElseIf j = WorksheetFunction.Max(iLastRow1, iLastRow2) Then
  'Range(strColResults &amp; i) = Range(strColResults &amp; i) &amp; strTemp
  Sheets(strSheetname2).Range(strCol2 &amp; i).Interior.Color = 255
  End If
  Next j
  Next i
  End Sub
  </code></pre>
  
  <p>How do I find the duplicates in column C and maybe color out the duplicates of column C of Sheet2 with column E of Sheet1?</p>