2

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
6
  • Not sure I understand. Where do you get 500 from using those two tables?
    – XOR LX
    Apr 2, 2015 at 10:48
  • My bad I changed the numbers when I realised I could mock the tables (didn't fancy making 500 rows!)
    – Jeff
    Apr 2, 2015 at 11:03
  • You could do this with VBa if that's an option
    – Dave
    Apr 2, 2015 at 11:24
  • Happy to but I would need a pretty good starting point as I've not used it for a long time.
    – Jeff
    Apr 2, 2015 at 11:26
  • You could also do this with PowerQuery, avoiding code. Apr 2, 2015 at 11:59

1 Answer 1

0

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

1
  • Perfect this works. Thanks for the helpful inline comments too. I'd vote up but can't yet :(
    – Jeff
    Apr 2, 2015 at 12:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .