Large data base with two sets incident numbers need to be joined to form one file

I am using excel 2016.

I have two large database files with data. The files have "unique" Incident Numbers associated with row data.
I have attached a sample file with a small amount of data which represents my files and the results I need.
The unique number column is highlighted in red in column A and F.
Column A has duplicate numbers and column F does not. But I need to have one file which combines all incident numbers found in Column A. No matter if they are duplicates are non duplicates. If Column A and F match, then data in columns J:S to be added to all Rows found in Columns A:D.

The number of rows of data in Columns A:D are 120,347 whereas the number of Rows in Columns F:S are 99,830.


Because Columns A:D have duplicates, there are 20,517 more rows of data then what is found in Columns F:S.

You could use this code:

Sub Macro2()
   Const adOpenStatic = 3
   Const adLockOptimistic = 3
   Const adCmdText = &H1
   
   Set cn = CreateObject("ADODB.Connection")
   Set rs = CreateObject("ADODB.Recordset")
   
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & ThisWorkbook.FullName & ";" & _
           "Extended Properties=""Excel 8.0;HDR=Yes;"";"
   
   rs.Open "select t1.[Incident number], t1.[VICTIM_NO], t1.[NIB_CODE], t1.[NIB_CODE_DESC], null, " _
         & "   t2.[Incident number], t2.[Report Type], t2.[Report Type Description], " _
         & "   t2.[Incident Status], t2.[Incident Status Description], t2.[Open Investigation], " _
         & "   t2.[Incident Occurred], t2.[Incident Reported], t2.[Location of incident], " _
         & "   t2.[Zip], t2.[ZONE], t2.[RPA], t2.[Mapping X co-ordinate], t2.[Mapping Y co-ordinate] " _
         & "from [Data Needing to Be Combined$A:D] t1 LEFT JOIN [Data Needing to Be Combined$F:S] t2 " _
         & "    on t1.[Incident number] = t2.[Incident number]", cn, adOpenStatic, adLockOptimistic, adCmdText

   ThisWorkbook.Sheets(2).Range("2:" & Rows.Count).ClearContents
   ThisWorkbook.Sheets(2).Range("A2").CopyFromRecordset rs
End Sub

Regards,
Antonio

Thank you for your help. Blessings,