Excel: Clone a list for each unique value in a different list

Question

I have two tables table1: list of unique Ids (list size is 3) and table2: list of items (list size is 3).

How could I create a third list where for each row in table 1 it adds all the rows from table2 with the unique id against it. So using the example numbers above we should end up with 9 rows, 3 rows for each unique id from table1.

Hope that makes sense, I also hope the below is readable!

Table1:

UID Header
UID1
UID2
UID3

Table2:

Header1      Header2
Name1        Value1       
Name2        Value2
Name3        Value3

Expected Result:

UIDH    Header1      Header2
UID1    Name1        Value1       
UID1    Name2        Value2
UID1    Name3        Value3
UID2    Name1        Value1       
UID2    Name2        Value2
UID2    Name3        Value3
UID3    Name1        Value1       
UID3    Name2        Value2
UID3    Name3        Value3

score 0 · Accepted Answer · edited Mar 20 '17 at 10:04

This does exactly what you've asked for

Option Explicit
Sub OoohEckPirates()

Dim table1Start As Integer
table1Start = 2                     ' UPDATE ME

Dim table2Start As Integer
table2Start = 7                     ' UPDATE ME

Dim resultsTableStart As Integer
resultsTableStart = 12              ' UPDATE ME


'Create the header
Range("A11").Value = "UID Header"   ' UPDATE ME
Range("B11").Value = "Name Header"  ' UPDATE ME
Range("C11").Value = "Value Header" ' UPDATE ME 

Dim header1Row As Integer
Dim header2Row As Integer
Dim resultsRow As Integer

Dim col  As Integer
col = 65                             'UPDATE ME - 65 = A, 66 = B, 67 = C. So, if your first column is A, set it to 65

Dim currentUid As String

header1Row = table1Start
resultsRow = resultsTableStart
Do While (Range("A" & header1Row).Value <> "")
    currentUid = Range("A" & header1Row).Value
    header2Row = table2Start

    Do While (Range(Chr(col) & header2Row).Value <> "")
            Range(Chr(col) & resultsRow).Value = currentUid
            Do While (Range(Chr(col) & header2Row).Value <> "")

                 Range(Chr(col + 1) & resultsRow).Value = Range(Chr(col) & header2Row).Value
                 col = col + 1
            Loop
    col = 65
    header2Row = header2Row + 1
    resultsRow = resultsRow + 1
    Loop


    header1Row = header1Row + 1
Loop

End Sub

Before you run it, take a back up though, just to be sure

How do I add VBA in MS Office?

Before

enter image description here

After

enter image description here

Excel: Clone a list for each unique value in a different list

1 Answers1