Hi,


this must be an extremely common problem that people solved daily but could not find any solutions.

I have two sets of classic product inventory data, one in a table on Sheet 1 and one in a table on Sheet 2. They come from two different computer systems and need to merge them into a single table on Sheet 3 so that ALL data (both columns and items) are present in Sheet 3 BUT only once (i.e. no duplicates of either columns or entries/items).

- some columns are present in both Sheet 1 and 2, some only on Sheet 1 and some only on Sheet 2
- same for the rows, some (most) items (which have their unique referencing IDs) are present in both Sheet 1 and 2, some only on Sheet 1 and some only on Sheet 2

The only values that can be reliably used to correlated sheet 1 and 2 which to create sheet 3 are:
- Unique IDs for products what appear in both tables
- Columns names/titles are always fixed for those columns that appear in both tables

How do can I do it?

Thank you 😊

P.S. I posted something similar here but was not what I was solving the problem completely: Creating third sheet with data from first and second sheets by matching a unique ID

you could do a index and match (vlookup) fairly easy which is probably how most people have done it for the last 20 years, but it can slow down a work book and you would need to do it again each time - this can be automated with VBA but takes some development time.

If the main goal is just merging the 2 tables i would look at power query - i have not spent that much time using it but for table merges it looks ideal.

You can also append data to the tables and refresh - not sure how you automate the joining new data extracts with power query but i am sure its possible you can even import directly from a folder.

Have a look at this blog post

https://www.myonlinetraininghub.com/...e-excel-sheets

Attached Files

Third sheet_Purfleet.xlsx (21.9 KB, 8 views)

Thank you Purflee

what I need is a solution where (because of the large amount of times data is pasted in) the ONLY TWO operations I would do are 1) pasting data into sheet 1 and 2) pasting data in sheet 2.

Even better, if instead of pasting data into sheet 1 and 2, the spreadsheet contains only the Sheet 3 and pulls data from two external spreadsheets (the equivalent of sheet 1 and sheet 2) which obviously are always named the same and always placed in the same folder as the Sheet 3 spreadsheet.
That way I would not even need to cut and paste. Only copy 2 spreadsheet files (one with Sheet 1 data and one with Sheet 2 data) and when I open the spreadsheet it automatically pulls the data from the other 2 files and shows the combined data.

Any formulas/solutions?

I also tried using this formula but it does not work at all. I literally copied/pasted and used the exact same data shown in there but gives a #value error: How to extract unique values from multiple columns in Excel?

You have 2 options as far as i know, but both will require some setup work.

1) VBA.
A macro could easily open an extract and combine data into 1 report with index and match, but you would need to know the file path and name and then be able to code it. not particularly hard but if you have never used VBA you would have a fairly steep learning curve

2) Power Query
As I said before I don’t know a lot about power query but from what i have read you can import extracts from a folder and hit refresh and the combining will be done - you would obviously need to do it once and set it up but then I should be a fairly quick refresh.

For both your recent threads power query will most likely be the best solution.
Could you supply Workbooks in the relevant threads,3 in one thread and 2 in the other?
Preferably showing the results you want.