Query to identify non-implicit relationship
First of all thanks in advance for reading this post.
Recently, I am exploring Power Pivot and its multiple options and relations with Traditional pivot Tables.
I would like to know if this request can be resolve through a formula or query. I am looking to identify a relationship that is non Implicit. I will try to explain myself.
I am working with a file that has around 8.000 rows and 15 columns, my question is regarding to the first two columns.
Client and Counterpart. It is possible that Client has multiple Counterpart including itself. What I am looking for is identify those relations that are not explicit. This is a sample.
Client Counterpart
A C
B D
C D
A C
B E
C A
A B
B D
C D
A D
B B
C A
A D
B E
C A
Using a Pivot table I got the following table moving both Fields on Rows
Row Labels
A
B
C
D
B
B
D
E
C
A
D
What I need to get is the following List. I believe that maybe a query can be useful, but no idea how I can apply this on excel.
With whom Client is non related.
Client Counterpart
A A
E
B C
C B
C
E
I will appreciate any help in this matter.
Thank you again.
I hope hear from anyone soon.
|