How to create composition of two relationships

[jdb]

How can I generate a table Comp{(y,z)} that is a composition of two other separate tables RelOne{(x,y)} and RelTwo{(y,z)}. The rows in each table represent ordered pairs of a many-to-many relationship.

RelOne is
(a, 1)
(b, 1)
(c, 1)
(a, 2)
(b, 2)

RelTwo is
(1, East)
(1, West)
(2, East)
(2, South)
(2, West)
(2, North)

There are no natural keys for either table.

The composition of RelOne and RelTwo would be

CompositionOneTwo is
(a, East)
(b, East)
(c, East)
(a, West)
(b, West)
(c, West)
(a, East)
(a, South)
(a, West)
(a, North)
(b, East)
(b, South)
(b, West)
(b, North)

I have five such situations, the actual tables have many hundreds of rows.

Thanks for any pointers.

 

[PCosta87]

Hi,

I know this isn't the VBA section and code is probably not what you are looking for but I will share a simple VBA based solution for this anyway:

Sub test()

    Dim c1, c2 As Range
  
    For Each c1 In Range("B3:B7")
        For Each c2 In Range("G3:G8")
            If c1.Offset(, 1) = c2.Offset(, -1) Then
                Cells(Rows.Count, "N").End(xlUp).Offset(1) = c1.Offset(, 1)
                Cells(Rows.Count, "O").End(xlUp).Offset(1) = c1
                Cells(Rows.Count, "P").End(xlUp).Offset(1) = c2
            End If
        Next c2
    Next c1
  
End Sub

I did it with fixed ranges but this can be changed to account for as many cells as you need.

See attached... just click the button.

Hope this helps

 

[jdb]

PCosta, THANKS! This is just what I was hoping for. I appreciate your help very much; saved my bacon at work. Your SUB worked perfectly. VBA is great--I sort of expected I'd need a macro.

 

[PCosta87]

PCosta, THANKS! This is just what I was hoping for. I appreciate your help very much; saved my bacon at work. Your SUB worked perfectly. VBA is great--I sort of expected I'd need a macro.

That is great
I'm glad it helped.