Help Merging Metrics from 2 different tables

[treym]

Let's see if I can explain this correctly. I have one table that is broken out by "Account" (ie, Retailer A), by month, by product UPC and includes Dollar Sales and Credits. I have another table (with data that comes from another source) that has additional metrics I'd like to merge into the first table: Unit Returns, Case Returns matched to those same retailers, months and products. I've made sure the Account "#" and UPC matches in both sheets. I'd like to be able to pull these into a single pivot table as opposed to two. There are some more complicated additions, but thats after I figure this out. I've included a simple mockup for reference.

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
3	Table One						Table Two
4	Date	Account	Account #	Sales	Credits		Date	Account	Account #	Unit Returns	Case Returns
5	1/1/2016	A	1234	100	25		1/1/2016	A	1234	5	25
6	2/1/2016	A	1234	200	50		2/1/2016	A	1234	8	32
7	3/1/2016	A	1234	300	25		3/1/2016	A	1234	10	56
8
9			NEW Merged Table
10			Date	Account	Account #	Sales	Credits	Unit Returns	Case Returns
11			1/1/2016	A	1234	100	25	5	25
12			2/1/2016	A	1234	200	50	8	32
13			3/1/2016	A	1234	300	25	10	56
Table Merge

 

[Meggesto]

So could you start with Table One Data and use the account number or account as a reference for a VLOOKUP? Then just VLOOKUP the Unit Returns and Case Returns thereby creating a third data set that resembles your NEW Merged Table. Then create your pivot table from that set of data?

 

[treym]

I thought of that but I have a few caveats (or I'm overthinking it). I literally have hundreds of products and thousands of rows of dates (years) and a hundred accounts. I wasn't sure if that complicated it or not. I guess I was thinking tings don't match 1:1. I may have more "dates" in the one column opposed to the other, so they wouldn't match row to row. I tend to think much more concretely, which I think limits my ability to figure it out. But essentially, yet, I want to run one pivot table report together to show the relationship.

 

[Meggesto]

I see; this can get very complicated. I'd need a larger sample the data in order to give you a solid answer. I just can't tell you off hand exactly how without a little trial and error. I'd recommend looking at INDEX and MATCH as an option to see if this can provide you with a solution.

 

[treym]

I could get a "masked" example to you and you could see what I mean. Let me know if you're interested and how to get it to you.