How to do Cartesian join that is based on the selected cell value

[rivers199]

Hello, I don't know how to do Cartesian join that needs to be based on the cell value. Following are the three tables:

Table 1
A B
--------
1 a
2 a
3 b
4 c
5 d
... (and so on)

Table 2
B value
------------
a 12
b 15
c 20
d 100

Table 3
B C value
------------------
a a1 111
a b1 222
a c1 333
b a6 193
b b7 278
c c4 323
c c5 567
d d1 444


Here are the results I need will be as follows:


A B C
----------------
1 12 111
1 12 222
1 12 333
2 12 111
2 12 222
2 12 333
3 15 193
3 15 278
4 20 323
4 20 567
5 100 444

What it happens is that table 1 of B is the foreign key of table 2 of B. So, whatever value is used in table 1 will get the corresponding value in table 2. As per table 1 of C, it will depend the value that is selected for the column B and then find all the corresponding combinations. Is there a simple way it can create all the combination?

Thank you so much

[hemesh]

Hello and welcome to the forum Rivers ! can you please upload a book with data and expected results

[rivers199]

Here's the file:
BK1.xlsx

Thank you so much.

[rivers199]

Here's the file:
BK1.xlsx

[shg]

There's a workbook at https://app.box.com/s/47b28f19d794b25511be that will do this:

	B	C	D
2	1	10	100
3	2	11	110
4	3	12	120
5	4	13	130
6	5	14	100
7	6		110
8	7		130
9	8		150
10	9		100
11	10		200
12	11		210
13	12		555
14	13		888
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22
23	1	10	100
24	1	10	110
25	1	10	120
26	1	10	130
27	1	10	100
28	1	10	110
29	1	10	130
30	1	10	150
31	1	10	100
32	1	10	200
33	1	10	210
34	1	10	555
35	1	10	888
36	1	11	100
37	1	11	110
38	1	11	120
39	1	11	130
40	1	11	100
41	1	11	110
42	1	11	130
43	1	11	150
44	1	11	100
45	1	11	200
46	1	11	210
47	1	11	555
48	1	11	888
49	1	12	100
50	1	12	110
51	1	12	120
52	1	12	130
53	1	12	100
54	1	12	110
55	1	12	130
56	1	12	150
57	1	12	100
58	1	12	200
59	1	12	210
60	1	12	555
61	1	12	888
62	1	13	100
63	1	13	110
64	1	13	120
65	1	13	130
66	1	13	100
67	1	13	110
68	1	13	130
69	1	13	150
70	1	13	100
71	1	13	200
72	1	13	210
73	1	13	555
74	1	13	888
75	1	14	100
76	1	14	110
77	1	14	120
78	1	14	130
79	1	14	100
80	1	14	110
81	1	14	130
82	1	14	150
83	1	14	100
84	1	14	200
85	1	14	210
86	1	14	555
87	1	14	888
88	2	10	100
89	2	10	110
90	2	10	120
91	2	10	130
92	2	10	100
93	2	10	110
94	2	10	130
95	2	10	150
96	2	10	100
97	2	10	200
98	2	10	210
99	…	…	…

[rivers199]

But, that's not the result I am looking for. The grid you showed me is the multiplication of all combinations. I am looking for the result that is based on the selection that is selected to use from the Table 1 and on. Thanks you very much.

A B C
-------------------
10 10 100
10 10 110
10 10 120
10 10 130
11 11 100
11 11 110
11 11 130
11 11 150
12 11 100
12 11 110
12 11 130
12 11 150
13 11 100
13 11 110
13 11 130
13 11 150
14 11 100
14 11 110
14 11 130
14 11 150
15 11 100
15 11 110
15 11 130
15 11 150
16 12 100
16 12 200
16 12 210
17 12 100
17 12 200
17 12 210
18 12 100
18 12 200
18 12 210
19 13 555
20 14 888

[MickG]

Try this:-
Results start sheet "Result" "A1"

Sub MG15Jan21
Dim Rng1 As Range
Dim Dn As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim temp As Range
Dim oSet As Long
Dim Ray
Dim Ac As Long
Dim n As Long
Dim Q
Dim A, B, C
Dim P As Long
With Sheets("TBL1")
Set Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With
With Sheets("TBL2")
Set Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("TBL3")
Set Rng3 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With

With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

Ray = Array(Rng1, Rng2, Rng3)
For Ac = 0 To UBound(Ray)
    For Each Dn In Ray(Ac)
        Select Case Ac
    Case 0: oSet = -1: A = Dn.Offset(, oSet)
    Case 1: oSet = 1: B = Dn.Offset(, oSet)
    Case 2: oSet = 2: C = Dn.Offset(, oSet)
    End Select
        
  Dim t
        
        If Not IsEmpty(Dn) Then Set temp = Dn
            If Not .Exists(temp.Value) Then
                .Add temp.Value, Array(A, B, C)
            Else
            Q = .Item(temp.Value)
                If Q(Ac) = Empty Then
                    Q(Ac) = Dn.Offset(, oSet)
                Else
                    Q(Ac) = Q(Ac) & "," & Dn.Offset(, oSet)
            End If
            .Item(temp.Value) = Q
            End If
    
    Next Dn
Next Ac
Dim Sp1 As Variant
Dim Sp2 As Variant
Dim Sp3 As Variant
Dim n1 As Long
Dim n2 As Long
Dim n3 As Long
Dim K

For Each K In .keys
    Sp1 = Split(.Item(K)(0), ",")
        Sp2 = Split(.Item(K)(1), ",")
            Sp3 = Split(.Item(K)(2), ",")
                For n1 = 0 To UBound(Sp1)
                    For n2 = 0 To UBound(Sp2)
                        For n3 = 0 To UBound(Sp3)
                            P = P + 1
                            Sheets("Result").Cells(P, "A") = Sp1(n1)
                            Sheets("Result").Cells(P, "B") = Sp2(n2)
                            Sheets("Result").Cells(P, "C") = Sp3(n3)
                        Next n3
                    Next n2
                Next n1
Next K
End With
End Sub

Regards Mick

[rivers199]

It works perfectly.

Thank you very much, Mick.

[MickG]

You're welcome
Regrds Mick