Access/Excel - merging two tables with mostly different fields/headings

Question

I have two sets of data in Excel. Most of the headings are different, however a few (e.g. "Name", "Project number") are present in both data sets.

The rows of data are also mostly different. One has 151 rows, the other has 751, however there are some (around 50-100) which appear in both (with the same Name/Project Number but different information accompanied).

Is there any way to get it so that the information from both excel sheets is merged into one table, including the headings from each, and the data from each with any duplicates removed but the information which is unique to each sheet (e.g. "Date" which is only present in Sheet1, and "Time" which is only present in Sheet2) included with the single row.

Answer 1

Hard to tell without seeing the data, but you if you treat the two data sets like tables, you should be able to use ADO and run a SQL query... something like How to name ADO connection while creating it in Excel VBA