Creating third sheet with data from first and second sheets by matching a unique ID

Hi,
I have a table on Sheet 1 and a table on Sheet 2.
Each entry in has a unique ID. That ID is that only thing that matches a product/row on Sheet 1 with a product on Sheet 2.

I want to have a Sheet 3 that merges data from Sheet 1 and 2 into a larger table that contains all the data (i.e. all the columns - but without obviously duplicating the columns).

IMPORTANT:
- The size of tables is often different.
- And not all entries on sheet 1 have a matching entry on sheet 2 and viceversa.

Any suggestions? Maybe using pivot tables or other "joining" functions?

Thank you

Update: also posted here: https://www.msofficeforums.com/excel...eets-into.html

Thank you,
I got it working by pasting the Unique IDs into sheet 3 but as I mentioned although Sheet 1 and 2 contain mostly the same IDs there are also very many IDs that are only on Sheet 1 and only on Sheet 2. That means that with that solution I would have to
1) either copy both columns of IDs into sheet 3 which then result into duplicates and twice the number of rows
2) manually somehow remove all the duplicates which with 10s of thousands of rows is not feaseable

Also in the uploaded sheet I only put a couple of columns in each sheet but there are 30 to 40 columns in each sheet and they all need to be combined without duplicating any column.

Also, with the final version, all I need to do is to just paste data into sheet 1 and sheet 2 and everything else i automated

Any further improvement of that version and/or any new solution?

Thank you again

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Hi Pepe,
not sure what you are referring to.

I had a conversation about this in previous posts and the other posts I made in another forums were flagged by someone else providing the links so I did not also add mine. Am I suppose to do that too? Add the same comment to the link even if someone else already has?

Also this is the only other post and I already and I did insert the link to this forum in my OP: https://www.msofficeforums.com/excel...eets-into.html

Can you please explain because am rather confused... :o

Have you read the link provided in post #4? I will provide it again https://www.excelguru.ca/content.php?184
If you take a moment to read it, you can draw your own conclusions

My approach in sheet 3:

List all ID in sheet 1 first, until ending, then

Check each ID in sheet 2 to get the unique ID in both sheets, then establish.

A2:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Then use VLOOKUP for the last 4 columns.