Compare several columns on two different sheets and copy the differences to another sheet

[sofas]

Hello, I have two excel sheets. Each sheet includes employee data for 2023 and another for 2024, with the same range from A10 to R3000, for example.
Contains duplicate data in both sheets. What I'm looking for is how to compare all columns and copy only the differences to a new sheet It already exists on the workbook named DATA

 

[sofas]

If this is difficult, is it possible to choose, for example, 5 and 6 different columns for comparison?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[alansidman]

Here is one way with Power Query

 

[jolivanes]

You have data in Column A in Sheet5 starting at A2.
If you have a Column with data in Column A in Sheet4 starting at A2 and run the following macro, you will get the missing data FROM Sheet5 in Sheet4, Column C.
Change all references where required.

Sub Compare_Sheets()
Dim sh4 As Worksheet, sh5 As Worksheet
Dim sh4Arr, sh5Arr, misV, i As Long
Set sh4 = Worksheets("Sheet4")
Set sh5 = Worksheets("Sheet5")
sh4Arr = sh4.Cells(1).CurrentRegion.Columns(1).Value
sh5Arr = sh5.Cells(1).CurrentRegion.Columns(1).Value
    For i = LBound(sh4Arr) + 1 To UBound(sh4Arr)
        If IsError(Application.Match(sh4Arr(i, 1), sh5Arr, False)) Then misV = misV & sh4Arr(i, 1) & "|"
    Next i
misV = Split(misV, "|")
On Error GoTo Its_All_Done
    sh4.Cells(sh4.Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(misV)).Value = Application.Transpose(misV)
Its_All_Done:
On Error GoTo 0
End Sub

I am sure that someone with 500+ posts can adapt it.

 

[sofas]

You have data in Column A in Sheet5 starting at A2.
If you have a Column with data in Column A in Sheet4 starting at A2 and run the following macro, you will get the missing data FROM Sheet5 in Sheet4, Column C.
Change all references where required.

Sub Compare_Sheets()
Dim sh4 As Worksheet, sh5 As Worksheet
Dim sh4Arr, sh5Arr, misV, i As Long
Set sh4 = Worksheets("Sheet4")
Set sh5 = Worksheets("Sheet5")
sh4Arr = sh4.Cells(1).CurrentRegion.Columns(1).Value
sh5Arr = sh5.Cells(1).CurrentRegion.Columns(1).Value
    For i = LBound(sh4Arr) + 1 To UBound(sh4Arr)
        If IsError(Application.Match(sh4Arr(i, 1), sh5Arr, False)) Then misV = misV & sh4Arr(i, 1) & "|"
    Next i
misV = Split(misV, "|")
On Error GoTo Its_All_Done
    sh4.Cells(sh4.Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(misV)).Value = Application.Transpose(misV)
Its_All_Done:
On Error GoTo 0
End Sub

I am sure that someone with 500+ posts can adapt it.

Sub Differences2022()
  Application.ScreenUpdating = False
  Set f1 = Sheets("2022"): Set f2 = Sheets("2023"): Set f3 = Sheets("Differences2022")
  j = f1.Range("A11:R" & f1.[A65000].End(xlUp).Row).Value
  K = f2.Range("A11:R" & f2.[A65000].End(xlUp).Row).Value
  Set arr = CreateObject("Scripting.Dictionary")
  For i = 2 To UBound(K): arr(UCase(Cpt(K(i, 1) & K(i, 2) & K(i, 12) & _
  K(i, 13) & K(i, 14) & K(i, 18)))) = "": Next i
  Cnt = 1
  Dim r
  ReDim r(1 To Application.Max(UBound(j), UBound(K)), 1 To UBound(j, 2))
  For i = 2 To UBound(j)
    If Not arr.exists(UCase(Cpt(j(i, 1) & j(i, 2) & j(i, 12) & j(i, 13) & _
                                       j(i, 14) & j(i, 18)))) Then
      For K = 1 To UBound(j, 2): r(Cnt, K) = j(i, K): Next K
      Cnt = Cnt + 1
    End If
  Next i
 
   Application.ScreenUpdating = False
  f3.[A4:R10000].ClearContents
  f3.[A4:R4].Value = f1.[A10:R10].Value
  f3.Range("A5").Resize(UBound(j, 1), UBound(j, 2)) = r
  f3.Columns("A:R").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

This is what I have been able to modify so far.