title: Best method of Excel Data Comparison present in 2 sheets
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/4379213/excel-macro-to-match-and-lineup-rows">Excel macro to match and lineup rows</a>  </p>
  </blockquote>
  
  
  
  <p>I have an Excel workbook with 2 Sheets with same set of columns but data arranged in different way. I need to compare Sheet 2 against Sheet 1 with the use of one or more Key column and identify unmatched records.</p>
  
  <p><strong>Data in sheet1:</strong></p>
  
  <pre><code>UserId Name Salary DeptId DeptName Location
  1      Loga 2000   1      HR       Chennai
  2      Mano 1500   2      PM       Mumbai
  3      Raj  2500   5      GM       Delhi
  </code></pre>
  
  <p><strong>Data in sheet1:</strong></p>
  
  <pre><code>UserId Name Salary DeptId DeptName Location
  2      Mano 1500   2      PM       Mumbai
  3      Raj  2500   5      GM       Delhi
  </code></pre>
  
  <p>first I need to match records based on UserId and DeptId, if matches in both the sheet Compare Salary  -> if salary matches store the record with UserId as Salary Matched.
  Similarly if UserId and DeptId matches in both the sheet then compare location -> if matches then store record with userid as location matched if not report as particular UserID not matched.,</p>
  
  <p>I am planning to use HLookUp in VBA Macro for comparing but seems lengthy process when no.of rows increases and reduces performance as well. 
  is there any suggestion available ?</p>