title: VBA to check duplicate based on multiple columns and show which row is duplicating with which one
body: <p>My excel has data from column A to AH and what I am trying to do is,</p>
  
  <ol>
  <li>Check the duplicate based on first three columns</li>
  <li>Insert a column as first column and mark the duplicate rows as duplicates along with the row number which is duplicating</li>
  </ol>
  
  <p>I have tried with the below codes which is based on single column but finding hard to make in multiple column and indicating the rows number, any thought will be appreciated.</p>
  
  <pre><code> Sub FindDuplicatesInColumn()
  'Declaring the lastRow variable as Long to store the last row value in the Column1
      Dim lastRow As Long
  
  'matchFoundIndex is to store the match index values of the given value
      Dim matchFoundIndex As Long
  
  'iCntr is to loop through all the records in the column using For loop
      Dim iCntr As Long
  
  'test the column A and insert a column or clear data
      If Range("A1").Value = "PDBC_PFX" Then
          Range("A1").EntireColumn.Insert
          Range("A1").Value = "DUPE_CHECK"
      Else
          Range("A2:A65000").Clear
      End If
  
  'Finding the last row in the Column B
      lastRow = Range("B65000").End(xlUp).Row
  
  'looping through the column B
      For iCntr = 1 To lastRow
          'checking if the cell is having any item, skipping if it is blank.
          If Cells(iCntr, 2) &lt;&gt; "" Then
              'getting match index number for the value of the cell
              matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 2), Range("B1:B" &amp; lastRow), 0)
              'if the match index is not equals to current row number, then it is a duplicate value
              If iCntr &lt;&gt; matchFoundIndex Then
                  'Printing the label in the column A
                  Cells(iCntr, 1) = "Duplicate"
              End If
          End If
      Next
  
  'auto fit column A
      Columns("A").AutoFit
  
  End Sub
  </code></pre>