Many-to-many from one table

0

0v3rl0rd

New member
Joined
Mar 8, 2015
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Pro Plus 2019
Hi


I need some help. Let's say I have a list of PEOPLE that traveled to some LOCATIONS. I've got a table in Excel - it's structured like this picture:
1.png

I want to transfer this to my database, but before I do that I need to create a "many-to-many" table. How should I do that?
I also created two tables:


PEOPLE >> with attributes: ID and PERSON_NAME
LOCATIONS >> with attributes: ID and LOCATION_NAME

The ID's are unique for each entry.
I need to create a third table called (e.g.) "people_locations" where I'll put the ID of the person in the first column and the ID of the location on the second column.


So for example, in this third table (which would actually represent a many-to-many relationship) the entries (for the above picture) should look something like this:
2.png


Any ideas how to do this?
 
Herbds7 said:
Excel 2010 with free Power Query Add-In.
Compatible with Office 2013 Pro Plus.
No formulas, no VBA macro.
If you get this far (show me!), we can tackle the ID part.
Click to expand...

Actually, I managed to do it via VBA macro code below, but, thank You anyway :)

Code: 
Sub many_to_many()

    Dim DataTable As Range
    Dim OutputRange As Range
    Dim RowOutput As Long
    Dim r As Long, c As Long
        
    On Error Resume Next
    Set DataTable = ActiveCell.CurrentRegion
    If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then
        MsgBox "Select cell in wanted table", vbCritical
        Exit Sub
    End If
    DataTable.Select
    
    Dim WS As Worksheet
    Set WS = Sheets.Add(After:=Worksheets(Worksheets.Count))
    
    Set OutputRange = Application.InputBox(prompt:="Select starting cell for output.", Type:=8)

    RowOutput = 2
    For r = 2 To DataTable.Rows.Count
        For c = 2 To DataTable.Columns.Count

            If DataTable.Cells(r, c) = "x" Then
                OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)
                OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)
                RowOutput = RowOutput + 1
            End If
            
        Next c
    Next r
    
End Sub
 
You must log in or register to reply here.
Back
Top