I have begun using a PowerPivot with the Excel 2013 Data Model to see if it could satisfy some of my needs.
Basically, in my first simple test I have two tables, with a unique relationship between the two :
Table A :
ID - Amount
Table B :
ID - Amount
What I want to do is :
1 - See what IDs from table A are missing from Table B
2 - See what IDs from table B are missing from Table A
3 - See what is the difference in the amount for IDs both in table A and table B.
I set a relationship based on the column ID between the two tables, and I can easily solve points 1 and 3, by adding two columns :
=RELATED(TableA[Amount]) => I change the name of this column to "AmountTableB"
=[Amount] - [AmountTableB]
But then if I try to add a column to my second set of data to solve my point 2, Power Pivot gives me an error telling me there is no relationship from table B to table A (only from A to B).
But if I try to add such relationship, Excel doesn't let me, saying I am trying to add a loop in the relationship...
So my answer is : how can I do that ?
Isn't it possible to create a two-way relationship between tables ?
My need is to find all differences between two lists. I know how to do that with MATCH, VLOOKUP, but a Power Pivot seemed a nicer way to see my data...