title: Compare 2 sheets with different headers
body: <p>I have 2 different files which have different headers, for example: </p>
  
  <pre><code>OldfileHeaders | NewFileheaders
  ID             | Test ID
  Date           | New date
  </code></pre>
  
  <p>and so on. I am trying to compare the data in both sheets and see if they match.  The rows of data may be in different order and the headers may also be in different order.</p>
  
  <p>So what I am trying to do is:
  1) define which headers match which headers between the 2 files
  2) find the ID from the oldfile and see if it is in the new file, if it is then see if the data under each header matches. If it doesn't then export that row of data to a new sheet add a column and label it "Missing".</p>
  
  <p>The Code So far:</p>
  
  <pre><code>Set testIdData = testIdData.Resize(testIdData.CurrentRegion.Rows.Count)
  
  Do Until sourceId.Value = ""
      datacopy = False
      ' Look for ID in test data
      Set cellFound = testIdData.Find(What:=sourceId.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
      If cellFound Is Nothing Then
      ' This entry not found, so copy to output
          datacopy = True
          outputRange.Resize(ColumnSize:=NUMCOLUMNS).Interior.Color = vbRed
      Else
          ' This assumes that columns are in same order
          For columnNum = 2 To NUM_COLUMNS_DATA
          ' No need to test the ID column
              If sourceId.Cells(ColumnIndex:=columnNum).Value &lt;&gt; cellFound.Cells(ColumnIndex:=columnNum).Value Then
                  outputRange.Cells(ColumnIndex:=columnNum).Interior.Color = vbYellow
                  datacopy = True
              End If
          Next columnNum
      End If
      If datacopy Then
          sourceId.Resize(ColumnSize:=NUMCOLUMNS).Copy
          outputRange.PasteSpecial xlPasteValuesAndNumberFormats
          Application.CutCopyMode = False
          Set outputRange = outputRange.Offset(RowOffset:=1)
          difference = difference + 1
      End If
      Set sourceId = sourceId.Offset(RowOffset:=1)
  Loop
  </code></pre>
  
  <p>This code works depending on me formatting the sheets in the correct order and changing the header names.</p>
  
  <p>I need help in defining which field names match which field names within the 2 sheets, and then searching the new sheet for each ID and seeing if the data in the corresponding cells match. If the ID is not in the sheet then output that row too a different sheet. If the id is present and there are differences in the cells then out put these to the shame sheet. I want to produce a tally of differences in each column.</p>