Query to identify non-implicit relationship

[LectorFiel]

Hello everyone,

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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

<tbody>
</tbody>

I will appreciate any help in this matter.

Thank you again.

I hope hear from anyone soon.

 

[MickG]

This is a VB solution that hopefully you may be able to use.
Results start "D1".

[COLOR="Navy"]Sub[/COLOR] MG21Feb44
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] kk          [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dic1        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd          [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("System.Collections.ArrayList")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         [COLOR="Navy"]If[/COLOR] Not Dic1.Contains(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR] Dic1.Add Dn.Offset(, 1).Value
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Nothing
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
Dic1.Sort
ReDim Ray(1 To Rng.Count, 1 To 2)
  Ray(1, 1) = "Client": Ray(1, 2) = "Counterpart"
   c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.keys
        Fd = True
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] kk [COLOR="Navy"]In[/COLOR] Dic1.toarray
            [COLOR="Navy"]If[/COLOR] Not Dic(k).exists(kk) [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ray(c, 1) = IIf(Fd, k, "")
                Ray(c, 2) = kk
                Fd = False
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] kk
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Range("D1").Resize(c, 2)
    .Value = Ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[billszysz]

Using Power Query this should be simple.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DistinctCounterpart = List.Sort(List.Distinct(Source[Counterpart])),
    Group = Table.Group(Source, {"Client"}, {{"lst", each List.Difference(DistinctCounterpart,List.Distinct(_[Counterpart])), type list}}),
    Table = Table.AddColumn(Group, "Custom", (x) => Table.FromColumns({{x[Client]},x[lst]}, {"Client", "CounterPart"})),
    RemoveCols = Table.SelectColumns(Table,{"Custom"}),
    Expand = Table.ExpandTableColumn(RemoveCols, "Custom", {"Client", "CounterPart"}, {"Client", "CounterPart"})
in
    Expand

Result of this code is exactly what you wish.( Table1 is your first table in post above)

Regards

 

[LectorFiel]

Hi Mick,

I appreciate your help with this matter.

I used the VB solution you created, It runs very well with the sample I described, but when I run this with the original Data, I got the message "Out of Memory". I mentioned that the original Data is around 8.000 Rows and 15 Columns.
I traced the VB solution and I found that the defined range is taking all rows on column A, I will suggest, use a function Counta() to identify the rows that contain data in column A, maybe in this way the solution does not show "Out of Memory".

A normal Pivot table shows the actual relationship. I am not sure what is behind Power Pivot, however, I was thinking if it is possible define two tables in this case Clients and Counterpart and Compare these two tables and find records without matches.

I am open to use macros, to identify these pending relations. I appreciate your thoughts, Thank you very much.

 

[LectorFiel]

Hi billszysz,

Thank you for answered me.

I have my file with the two columns I described before. I add this to Data Model.
When I go to Manage Data Model, I can see the data, but from this point I did not know what I have to do, how to created the query to use the code you posted.
Sorry for my ignorance, but I really appreciate if you can provide me some directions to access the right options to do that.

Thank you very much.

 

[MickG]

I'm sorry it did not work for you
NB:- The code will only look at the rows with data in then, in column "A/B"
also, the code only looks at columns "A and B"
I have just run the code with 16 K rows with no trouble

What line does the code stop on when you run it ????

 

[LectorFiel]

Dear billszysz,

I try to put the Data in this way and I created the following relationship on Powerpivot. Hopefully, this can help me to use your analysis.

I set in another sheet, each column from the original data without duplicates, Therefore I got a Table Client and Table Counterpart. after this on Mange data Model, I created the following relationship.

Table1 Relationship to TblClient
Client N to 1 Client
Counterpart

Table1 Relationship to TblCounterpart
Client
Counterpart N to 1 Counterpart

Please let me know if I am doing things on the right direction.

Thank you.

 

[LectorFiel]

Dear Mick,

The message appears on this line

ReDim Ray(1 To Rng.Count, 1 To 2)

Thank you

 

[MickG]

If you step through the code (Keep pressing F8 with code window open ( "Alt + F11")) until that line, and then, if you hold your curser over "Rng.count", how many rows does it say ????

 

[LectorFiel]

Rng.Count = 4457