Combining two sets of data with different categories (some that overlap)

[justinx]

Hello,

Say I have two sets of data (on different sheets or workbooks):

Food	John
Cheese	10%
Pizza	12%
Salad	1%
Bread	50%

Food	Jill
Salad	95%
Candy	50%
Turkey	4%
Cheese	24%

What I would like to do is combine these two data sets into one sheet that looks like:

Food	John	Jill
Cheese	10%	24%
Pizza	12%
Salad	1%	95%
Bread	50%
Candy		50%
Turkey		4%

How can I achieve this? Thank you in advance!

[Estevaoba]

Hello, justinx.
I guess a possible solution will most likely depend on the data structure that you use.
For example, if John shows in Sheet1 and Jill in Sheet2, we could try the INDIRECT function to build sheet name in the formula and it would change accordingly as you copy the formula to the next column.
But if you deal with a lot of sheets, this functin would slow down your spreadsheet.

That said, it would be interesting if you could follow instructions in yellow banner above and upload a sample workbook (minding the part that says realistic & representative).

[Excelko]

Hi justinx,
I agree that woth INDIRECT you can do it if your dataset is not too big.
Here is short video showing how to do it:
https://m.youtube.com/watch?v=ttm-hHkYXBE

If you have huge data in those sheets maybe you can use there Power Query to combine it instead of using functions that would slow down your file.