2 matrices with two categorical variables/columns (product and country) and up to ten real-valued variables to be reconciled (they can be also negative). Datasets can have unique categorical variables, i.e. matrix A with product B, which is nowhere to be found in matrix B or for instance country C for product B in only one of the matrices. That means, I have to extract all unique combinations of product-country from both datasets upon which a reconciliation is to be made.
My current solution
0) create third categorical variable prodcountry as a combination of product ID and country in both datasets
1) sort data in pivot table by prodcountry (a product can be sold in a country more times)
2) vlookup all prodcountries from dataset A in the dataset B and return its numerical value MINUS numerical value from dataset A. Do this for all variables/columns and all rows. Then vlookup prodcountries from dataset B in dataset A and do the same. Unfortunately, this results in duplicates, but I haven't found an automatic (formula-based or simple macro) way of converting all unique prodcountries from two columns into one.
What I would like to achieve:
My current solution
0) create third categorical variable prodcountry as a combination of product ID and country in both datasets
1) sort data in pivot table by prodcountry (a product can be sold in a country more times)
2) vlookup all prodcountries from dataset A in the dataset B and return its numerical value MINUS numerical value from dataset A. Do this for all variables/columns and all rows. Then vlookup prodcountries from dataset B in dataset A and do the same. Unfortunately, this results in duplicates, but I haven't found an automatic (formula-based or simple macro) way of converting all unique prodcountries from two columns into one.
What I would like to achieve:
Last edited:
