how to find all possible interdependencies

[illusionek]

Hello

I have a list of customers who buy from me various products. The deal is I need to send orders in full so I have to have all products in stock. I need to identify all products that are interdependent ie they go together regardless whether always together or if customers/orders are linked together by one product only.

In the example below I see I can always send Product 4 on its own but when it comes to Products 1, 2 and 3, I always need to have all of them available otherwise I wont be able to fulfil orders. Thats because customer A takes Product 2, Customer B takes Product 3 and both take Product 1.

Financially I am better off to have either Products 1, 2 and 3 or none of them but I dont know how to create list of combinations. I have 200 products and over 1 000 customers.

Customer Name	Product
Customer A	Product 1
Customer A	Product 2
Customer B	Product 1
Customer B	Product 3
Customer C	Product 4
Results:
Product 1, Product 2, Product 3
Product 4

 

[lrobbo314]

This seems to work using Power Query.

FY2022 SIU Assignment Preference Request(1-10).xlsx
	A	B	C	D	E
1	Customer Name	Product			Count
2	Customer A	Product 1			Product 1, Product 2, Product 3
3	Customer A	Product 2			Product 6, Product 7
4	Customer B	Product 1			Product 4
5	Customer B	Product 3			Product 5
6	Customer C	Product 4
7	Customer D	Product 5
8	Customer E	Product 6
9	Customer E	Product 7
10	Customer F	Product 6
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Product"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Customer Name=text, Product=text]}}),
    Over1 = Table.SelectRows(Group, each [Count] > 1),
    ExpandCust = Table.ExpandTableColumn(Over1, "Data", {"Customer Name"}, {"Customer Name"}),
    MQ0 = Table.NestedJoin(ExpandCust, {"Customer Name"}, Source, {"Customer Name"}, "ExpandCust", JoinKind.LeftOuter),
    ExpandedExpandCust = Table.ExpandTableColumn(MQ0, "ExpandCust", {"Customer Name", "Product"}, {"Customer Name.1", "Product.1"}),
    Group1 = Table.Group(ExpandedExpandCust, {"Product"}, {{"Count", each _, type table [Product=text, Count=number, Customer Name=nullable text, Customer Name.1=text, Product.1=text]}}),
    Reduce = Table.TransformColumns(Group1,{{"Count", each Text.Combine(List.Distinct(_[Product.1]),", ")}}),
    RC = Table.RemoveColumns(Reduce,{"Product"}),
    Single = Table.FromList(List.Difference(List.Distinct(Source[Customer Name]), List.Distinct(ExpandCust[Customer Name])),null,{"Customer"}),
    MQ1 = Table.NestedJoin(Single, {"Customer"}, Source, {"Customer Name"}, "Single", JoinKind.LeftOuter),
    ExpandedSingle = Table.ExpandTableColumn(MQ1, "Single", {"Product"}, {"Count"}),
    RC1 = Table.RemoveColumns(ExpandedSingle,{"Customer"}),
    Append = Table.Combine({RC, RC1})
in
    Append

 

[illusionek]

Many thanks for looking into this. Unfortunately the above doesnt work on a larger data set. Pls see below link to a file with example

example file.xlsx

drive.google.com

 

[lrobbo314]

I took the larger data set you provided and used Python to write code to do what you asked for... and I got the same results.

So, I'm not sure what isn't working the way you want it to.

Can you provide an example, possibly on a smaller dataset, that shows how this is failing?

 

[illusionek]

Apologies, I provided the file but no proper feedback, on the second tab of provided file you can see results of the query. As you can see the same products are repeating across multiple rows/combinations for example rows 39 and 40 are almost identical with the exception that row 39 shows Product 10316, which isnt in row40. Also product 10006 shows in row40 and row37.

The desired outcome would be that each product appears only in one combination that includes all possible interdependencies.

I could trim the data set but my concern is it wouldnt reflect reality and the code worked very well on a small dummy data set but not on real life example.

 

[lrobbo314]

Let me know if this is the correct output.

example file.xlsx
	A
1	Results
2	Product 10045,Product 10158,Product 12946,Product 13044,Product 32791,Product 10250,Product 10275,Product 32461,Product 32790,Product 12086,Product 12941,Product 12943,Product 12945,Product 32810,Product 33210,Product 33211,Product 10034,Product 13172,Product 10138,Product 10291,Product 10352,Product 10775,Product 10776,Product 10778,Product 13174,Product 32792,Product 10037,Product 10071,Product 10109,Product 10177,Product 10208,Product 10277,Product 12390,Product 12938,Product 13158,Product 13159,Product 13937,Product 33518,Product 10126,Product 12389,Product 10774,Product 13758,Product 10086,Product 10276,Product 12079,Product 10278,Product 10360,Product 12087,Product 13171,Product 13176,Product 10035,Product 10361,Product 11217,Product 11218,Product 12054,Product 12225,Product 12937,Product 33280,Product 10786,Product 10148,Product 10254,Product 10081,Product 10152,Product 10196,Product 10210,Product 10407,Product 10899,Product 11120,Product 13045,Product 32343,Product 32345,Product 32471,Product 32473,Product 10144,Product 13837,Product 32193,Product 10270,Product 32350,Product 32460,Product 32803,Product 32955,Product 32956,Product 33355,Product 33356,Product 12567,Product 32355,Product 10139,Product 10151,Product 13500,Product 33102,Product 10787
3	Product 10071,Product 10109,Product 10126,Product 10138,Product 10177,Product 10277,Product 10291,Product 10778,Product 10792,Product 11436,Product 12941,Product 13044,Product 13045,Product 10073,Product 10775,Product 11188,Product 11737,Product 12079,Product 13159,Product 13758,Product 13937,Product 10208,Product 10794,Product 12691,Product 13158,Product 10340,Product 10770,Product 10772,Product 10776,Product 10793,Product 10143,Product 10144,Product 10139,Product 10141,Product 10320,Product 10771,Product 10773,Product 10789,Product 11189,Product 11190,Product 11191,Product 11386,Product 11387,Product 11388,Product 11780,Product 12693,Product 13174,Product 13178,Product 10037,Product 12946,Product 11437,Product 10180,Product 10780,Product 11779
4	Product 10034,Product 10081,Product 10086,Product 10141,Product 10148,Product 10152,Product 10196,Product 10210,Product 10275,Product 10361,Product 11215,Product 11216,Product 11217,Product 11218,Product 12086,Product 12937,Product 12938,Product 13045,Product 32459,Product 32790,Product 32791,Product 32792,Product 32810,Product 10139,Product 10352,Product 10360,Product 12567,Product 13044
5	Product 10035,Product 10037,Product 10122,Product 10277,Product 10278,Product 10291,Product 10352,Product 10776,Product 13044,Product 13158,Product 13159,Product 13171,Product 13758,Product 32791,Product 10073,Product 10109,Product 10126,Product 10138,Product 10176,Product 10787,Product 10367
6	Product 10037,Product 10073,Product 10109,Product 10126,Product 10138,Product 10790,Product 12691,Product 13044,Product 10177,Product 10208,Product 10775,Product 13159,Product 10275,Product 10352,Product 10770,Product 10772,Product 12693,Product 13941,Product 11737,Product 13158
7	Product 13814,Product 13815,Product 13816,Product 13818,Product 13819,Product 13822,Product 13823,Product 13824,Product 13825,Product 33088,Product 33089,Product 33090,Product 33091,Product 33092,Product 33093,Product 33094,Product 33095,Product 13820
8	Product 10271,Product 12816,Product 10126,Product 10267,Product 12815,Product 10004,Product 32343,Product 32345,Product 10006,Product 10109,Product 10270,Product 10316,Product 10366,Product 10367,Product 12116,Product 13095,Product 13937
9	Product 10138,Product 10174,Product 10275,Product 10277,Product 10037,Product 10177,Product 10291,Product 10775,Product 10776,Product 12946,Product 13044,Product 13158,Product 10774,Product 10787
10	Product 10047,Product 10089,Product 10146,Product 10250,Product 13173,Product 13175,Product 10254,Product 12937,Product 12938,Product 10003,Product 10006,Product 10273,Product 10291,Product 32791
11	Product 10034,Product 10158,Product 12943,Product 13044,Product 32345,Product 32810,Product 33163,Product 33210,Product 33518,Product 13174,Product 33134
12	Product 32343,Product 32353,Product 10037,Product 10774,Product 10775,Product 13044,Product 13045,Product 13837,Product 32345,Product 32348,Product 32352
13	Product 10289,Product 10267,Product 10270,Product 10002,Product 10109,Product 10288,Product 13095,Product 13937,Product 13938,Product 33341
14	Product 11898,Product 12249,Product 12250,Product 12553,Product 14080,Product 14081,Product 14082,Product 32461,Product 33300,Product 10138
15	Product 10086,Product 10151,Product 10197,Product 10275,Product 10352,Product 11309,Product 13006,Product 13817,Product 13821,Product 32473
16	Product 10138,Product 10139,Product 10141,Product 10159,Product 10275,Product 10319,Product 10899,Product 11309,Product 32459,Product 32803
17	Product 10126,Product 10316,Product 11739,Product 11740,Product 10006,Product 10071,Product 10270,Product 11742,Product 13937,Product 10109
18	Product 10250,Product 10275,Product 10278,Product 10291,Product 10352,Product 10778,Product 11120,Product 12588,Product 32810,Product 33207
19	Product 10036,Product 10109,Product 10126,Product 10175,Product 10178,Product 10277,Product 10778,Product 13937
20	Product 10275,Product 10774,Product 10776,Product 12086,Product 12943,Product 13044,Product 13174,Product 13836
21	Product 10063,Product 10201,Product 10343,Product 12939,Product 13903,Product 10061,Product 13937,Product 13095
22	Product 10071,Product 10779,Product 10784,Product 12086,Product 13045,Product 32810,Product 32955,Product 32956
23	Product 33535,Product 32697,Product 32698,Product 32699,Product 33221,Product 32700
24	Product 10275,Product 10278,Product 10352,Product 12836,Product 32473,Product 32803
25	Product 10180,Product 10780,Product 13009,Product 13044,Product 13837
26	Product 10267,Product 10288,Product 10367,Product 13095,Product 13942
27	Product 10109,Product 10144,Product 13177,Product 13178,Product 13758
28	Product 10248,Product 33210,Product 33211,Product 10258
29	Product 10275,Product 13004,Product 32459,Product 10319
30	Product 10250,Product 10252,Product 12591,Product 13044
31	Product 10003,Product 10127,Product 10254,Product 13935
32	Product 10224,Product 10268
33	Product 10270,Product 10317
34	Product 10277,Product 11432
35	Product 10115,Product 10316
36	Product 10006,Product 13699
37	Product 10092,Product 33028
38	Product 10227
39	Product 10266
40	Product 10263
41	Product 12932
42	Product 12337
43	Product 10297
44	Product 13345
45	Product 10221
46	Product 10261
47	Product 10269
48	Product 11656
49	Product 10110
Sheet4

Sub MainX()
Dim AR() As Variant:        AR = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim Used As Object:         Set Used = CreateObject("System.Collections.ArrayList")
Dim TMP As Object:          Set TMP = CreateObject("System.Collections.ArrayList")
Dim TC As Object:           Set TC = CreateObject("System.Collections.ArrayList")
Dim RES As Object:          Set RES = CreateObject("System.Collections.ArrayList")

For i = 1 To UBound(AR)
    If Not Used.contains(AR(i, 2)) Then
        FilterX TMP, AR(i, 2), AR
        Matches TMP, AR, TC, RES
        For Each t In TC
            Used.Add t
        Next t
    End If
Next i

Range("E1").Resize(RES.Count).Value = Application.Transpose(RES.toArray)
End Sub

Sub FilterX(TMP As Object, Item As Variant, AR() As Variant)
TMP.Clear

For i = 1 To UBound(AR)
    If AR(i, 2) = Item Then TMP.Add AR(i, 1)
Next i
End Sub

Sub Matches(TMP As Object, AR() As Variant, TC As Object, RES As Object)
TC.Clear

For Each t In TMP
    For i = 1 To UBound(AR)
        If AR(i, 1) = t Then
            If Not TC.contains(AR(i, 2)) Then TC.Add AR(i, 2)
        End If
    Next i
Next t
RES.Add Join(TC.toArray, ",")
End Sub

 

[lrobbo314]

Looking at this more, I don't think that the last one is quite right. Don't really think this one is 100% either, but I feel like it's getting closer.

example%20file (version 2).xlsb
	A
1	D
2	Product 10045, Product 10158, Product 12946, Product 13044, Product 32791, Product 10250, Product 10275, Product 32461, Product 32790, Product 12086, Product 12941, Product 12943, Product 12945, Product 32810, Product 33210, Product 33211, Product 10034, Product 13172, Product 10138, Product 10291, Product 10352, Product 10775, Product 10776, Product 10778, Product 13174, Product 32792, Product 10037, Product 10071, Product 10109, Product 10177, Product 10208, Product 10277, Product 12390, Product 12938, Product 13158, Product 13159, Product 13937, Product 33518, Product 10126, Product 12389, Product 10774, Product 13758, Product 10086, Product 10276, Product 12079, Product 10278, Product 10360, Product 12087, Product 13171, Product 13176, Product 10035, Product 10361, Product 11217, Product 11218, Product 12054, Product 12225, Product 12937, Product 33280, Product 10786, Product 10148, Product 10254, Product 10081, Product 10152, Product 10196, Product 10210, Product 10407, Product 10899, Product 11120, Product 13045, Product 32343, Product 32345, Product 32471, Product 32473, Product 10144, Product 13837, Product 32193, Product 10270, Product 32350, Product 32460, Product 32803, Product 32955, Product 32956, Product 33355, Product 33356, Product 12567, Product 32355, Product 10139, Product 10151, Product 13500, Product 33102, Product 10787, Product 33163, Product 33134, Product 10073, Product 10793, Product 11188, Product 11436, Product 11737, Product 10143, Product 10792, Product 10794, Product 12691, Product 10174, Product 10790, Product 10252, Product 12591, Product 10141, Product 11215, Product 11216, Product 32459, Product 13836, Product 10340, Product 10770, Product 10772, Product 10320, Product 10771, Product 10773, Product 10789, Product 11189, Product 11190, Product 11191, Product 11386, Product 11387, Product 11388, Product 11780, Product 12693, Product 13178, Product 10122, Product 13941, Product 11437, Product 10176, Product 10180, Product 10780, Product 13009, Product 32348, Product 32352, Product 32353, Product 11309, Product 10003, Product 10006, Product 10047, Product 10273, Product 10089, Product 10146, Product 13173, Product 13175, Product 12588, Product 33207, Product 13004, Product 12836, Product 10197, Product 13006, Product 13817, Product 13821, Product 10319, Product 10159, Product 11898, Product 12249, Product 12250, Product 12553, Product 14080, Product 14081, Product 14082, Product 33300, Product 10779, Product 10784, Product 10248, Product 10036, Product 10175, Product 10178, Product 10002, Product 10004, Product 11739, Product 11740, Product 11742, Product 10316, Product 13177, Product 10288, Product 10289, Product 13095, Product 13938, Product 33341, Product 10271, Product 10366, Product 10367, Product 12116, Product 11432, Product 10061, Product 10063, Product 10267, Product 10127, Product 13935, Product 11779, Product 10317
3	Product 10006, Product 13699, Product 10071, Product 10126, Product 10270, Product 11739, Product 11740, Product 11742, Product 13937, Product 10109, Product 10271, Product 10316, Product 10366, Product 10367, Product 12116, Product 13095, Product 10003, Product 10047, Product 10254, Product 10273, Product 10291, Product 12938, Product 32791
4	Product 10267, Product 10004, Product 10270, Product 10289, Product 10126, Product 10271, Product 12815, Product 10002, Product 10288, Product 10367, Product 13095, Product 13942, Product 10109, Product 13937, Product 13938, Product 33341, Product 10122, Product 10006, Product 10316, Product 10366, Product 12116, Product 10063
5	Product 13814, Product 13815, Product 13816, Product 13818, Product 13819, Product 13822, Product 13823, Product 13824, Product 13825, Product 33088, Product 33089, Product 33090, Product 33091, Product 33092, Product 33093, Product 33094, Product 33095, Product 13820
6	Product 10271, Product 12816, Product 10126, Product 10267, Product 12815, Product 10004, Product 32343, Product 32345, Product 10006, Product 10109, Product 10270, Product 10316, Product 10366, Product 10367, Product 12116, Product 13095, Product 13937
7	Product 10115, Product 10316, Product 10109, Product 10126, Product 10270, Product 11739, Product 11740, Product 10006, Product 10271, Product 10366, Product 10367, Product 12116, Product 13095, Product 13937
8	Product 10063, Product 10201, Product 10343, Product 12939, Product 13903, Product 10061, Product 13937, Product 13095
9	Product 33535, Product 32697, Product 32698, Product 32699, Product 33221, Product 32700
10	Product 10248, Product 33210, Product 33211, Product 10258
11	Product 10224, Product 10268
12	Product 10092, Product 33028
13	Product 10227
14	Product 10266
15	Product 10263
16	Product 12932
17	Product 12337
18	Product 10297
19	Product 13345
20	Product 10221
21	Product 10261
22	Product 10269
23	Product 11656
24	Product 10110
Sheet6

Sub TTT()
Dim AR() As Variant:        AR = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim Prod As Object:         Set Prod = CreateObject("System.Collections.ArrayList")
Dim Cust As Object:         Set Cust = CreateObject("System.Collections.ArrayList")
Dim RES As Object:          Set RES = CreateObject("System.Collections.ArrayList")
Dim Used As Object:         Set Used = CreateObject("System.Collections.ArrayList")
Dim SP() As String

For i = 1 To UBound(AR)
    If Not Used.contains(AR(i, 2)) Then
        GetCust AR(i, 2), AR, Cust
        GetProd AR, Cust, Prod
        FF Cust, Prod, AR, RES
        Prod.Clear
        Cust.Clear
        SP = Split(RES(RES.Count - 1), ", ")
        For Each s In SP
            Used.Add s
        Next s
    End If
Next i

Range("E2").Resize(RES.Count).Value2 = Application.Transpose(RES.toArray)
End Sub

Sub GetCust(Item As Variant, AR() As Variant, Cust As Object)

For i = 1 To UBound(AR)
    If AR(i, 2) = Item Then Cust.Add AR(i, 1)
Next i
End Sub

Sub GetProd(AR() As Variant, Cust As Object, Prod As Object)

For Each c In Cust
    For i = 1 To UBound(AR)
        If AR(i, 1) = c Then
            If Not Prod.contains(AR(i, 2)) Then Prod.Add AR(i, 2)
        End If
    Next i
Next c
End Sub

Sub FF(Cust As Object, Prod As Object, AR() As Variant, RES As Object)
Dim LC As Object:   Set LC = CreateObject("System.Collections.ArrayList")
Dim LP As Object:   Set LP = CreateObject("System.Collections.ArrayList")
Dim CC As Integer:  CC = -1
Dim PC As Integer:  PC = -1

Do While LP.Count <> PC And LC.Count <> CC
    CC = LC.Count
    PC = LP.Count
    GCT Prod, LC, AR
    GPT LP, LC, AR
Loop

RES.Add Join(LP.toArray, ", ")
End Sub

Sub GCT(Prod As Object, LC As Object, AR() As Variant)

For Each p In Prod
    For i = 1 To UBound(AR)
        If AR(i, 2) = p Then
            If Not LC.contains(AR(i, 1)) Then LC.Add AR(i, 1)
        End If
    Next i
Next p
End Sub

Sub GPT(LP As Object, LC As Object, AR() As Variant)

For Each c In LC
    For i = 1 To UBound(AR)
        If AR(i, 1) = c Then
            If Not LP.contains(AR(i, 2)) Then LP.Add AR(i, 2)
        End If
    Next i
Next c
End Sub