Excel duplicate data multiple tables

Question

So usually I know what I'm trying to do and can google it and figure it out. But this, I don't even know what it's called that I'm trying to do. I have two sheets in excel (or tables in Access, whichever is easiest for you). Table A has 315 numbers and Table 2 has 146 people. I need to merge the two tables so that I have two columns. One column with each account and the other with each person. So I'll basically end up with about 46,000 rows. So:

Table 1:

number
1
2
3
4

Table 2:

person

A

B

C

D

Intended result:

Table 3:

person number

A | 1

A | 2

A | 3

A | 4

B | 1

B | 2

B | 3

B | 4

C | 1

C | 2

C | 3

C | 4

And so on. If I was doing PHP with mysqli, I'd just output a for loop but I only have access/excel available to me here and I don't know enough VBA to make my own macro (if I knew what I was trying to do, here is where I'd google it). So, I appreciate any willingness to help even though I'm not even providing code that I've tried simply because I don't know where to start.

Do you have Excel 2016 or Excel 2013 with Power Query? If not, VBA is probably your best bet. — Alexis Olson
– Alexis Olson, Commented Sep 20, 2016 at 1:44
Neither. 2010. I'm assuming VBA is going to be my best option, some sort of macro, I just don't know what I'm googling for. Baha. — Aaron
– Aaron, Commented Sep 20, 2016 at 2:09
just a note that you would Google for: "cross join" and (Excel or Access) — eshwar
– eshwar, Commented Sep 20, 2016 at 2:57
And that's what I didn't know. Didn't know what term to use. Thanks! — Aaron
– Aaron, Commented Sep 20, 2016 at 11:33

YowE3K · Accepted Answer · 2016-09-20 02:33:25Z

2

Using Access, the query would be

SELECT Table2.person, Table1.number INTO Table3
FROM Table1, Table2;

edited Sep 20, 2016 at 2:33

answered Sep 20, 2016 at 2:22

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

Ha! Didn't realize access was in the equation - that's definitely the ideal solution: a simple cross join straight at the source =)

Aaron Over a year ago

I'm ashamed that I didn't think of that. Doh.

YowE3K Over a year ago

@Aaron - I'm even more amazed that I did think of it!

Alexis Olson · Accepted Answer · 2016-09-20 02:21:31Z

1

This is pretty straightforward with a loop.

Here's a basic example of what it would look like reading these in as ranges.

Sub Looping()
Dim n As Integer
Dim rng1 As Range, rng2 As Range

Set rng1 = Range("A1:A4")
Set rng2 = Range("B1:B4")

n = 0
For Each i In rng1
    For Each j In rng2
        n = n + 1
        Cells(n, "D").Value = i.Value & j.Value
    Next j
Next i

End Sub

Where your 1,2,3,4 is in A1:A4, your A,B,C,D is in B1:B4, and the output goes to D1:D16.

As @Mat'sMug said though, it is probably better to use tables than to have to define your exact ranges.

answered Sep 20, 2016 at 2:21

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

1 Comment

Aaron Over a year ago

I'm giving you the vote because you gave me the code. Had to adjust it a little (needed the i value and j value in separate columns, but that was an easy adjustment). I appreciate your help in helping the fledgling programmer. :)

Mathieu Guindon · Accepted Answer · 2016-09-20 02:15:16Z

1

Format these lists as tables, so the VBA code can use ListObject objects; you can take their Range to literally dump them into Variant() arrays, and then you just set up your nested loops; you'll want to switch off Application.ScreenUpdating, because you're going to iterate your "AccountList" while iterating your "PersonList", and the inner loop's body will be writing to the target Worksheet; and then you can turn the result into a ListObject before switching Application.ScreenUpdating back on. Or, let Excel repaint itself, but display a %completion status in the Application.Statusbar so the user knows the macro is working... but showing status and refreshing the screen will take longer than if you don't.

answered Sep 20, 2016 at 2:15

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

4 Comments

Aaron Over a year ago

So you're saying have VBA pull both tables into arrays and then continuously loop/dump the array? (Once again, if this was PHP, I can completely picture what you're saying). Are you willing to give me a code snippet start? (I knew I shouldn't have skipped VB class...)

Aaron Over a year ago

Mind you, the data is fixed (I know all the data), I'm just looking for the fastest way for me to combine and duplicate instead of me copying and pasting 146 items 315 times.

Mathieu Guindon Over a year ago

@Aaron straight-up SQL is the easiest, simplest, fastest way to do this - I only hadn't realized you had the data in MS-Access. YowE3K has your best solution :)

Aaron Over a year ago

No prob. I appreciate your willingness. I'm ashamed I didn't think about the join. haha.

Andrew Redican · Accepted Answer · 2016-11-28 20:00:58Z

0

That's easy.

What you want to do is iterate item by item of the first column and for every iteration it will iterate the second column.

Something like this in Excel VBA:

I = 1
III = 1
Do While Not Range("A" & I) = ""
       II = 1
       Do While Not Range("B" & II) = ""

           /Your Code Goes Here
           /For example:
           Range("C" & III) = Range("A" & I)
           Range("D" & III) = Range("B" & II)
           III = III + 1

           II = II + 1
       Loop
   I = I + 1
Loop

Cheers! You'll get there.

edited Nov 28, 2016 at 20:00

answered Nov 28, 2016 at 19:55

Andrew Redican

395 bronze badges

Collectives™ on Stack Overflow

Excel duplicate data multiple tables

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related