PDA

View Full Version : Simple Merge of two lists/tables



davew
10-03-2012, 04:49 AM
Hi,

I’ve got what should be a simple problem but I’m struggling with it.

I have two tables. The first table contains three fruit: orange; apple, and pear. The second table contains 3 colours: red; yellow, and green.

I’d like a macro to cycle through and create a new list of two columns* like so (i.e. creates a list of every combination):

Colour
Fruit
red
orange
red
apple
red
pear
yellow
orange
yellow
apple
yellow
pear
green
orange
green
apple
green
pear

*I had some trouble getting it to display as two columns in this post but hopefully you get what I mean.

Once I’ve mastered this I can apply to larger tables.

Any help would be greatly appreciated.

Thanks

davew

GarysStudent
10-03-2012, 06:50 AM
With colors in column A and fruit in column B:

Sub MixMatch()
K = 1
For L = 1 To 3
aa = Cells(L, 1)
For M = 1 To 3
bb = Cells(M, 2)
Cells(K, 3) = aa
Cells(K, 4) = bb
K = K + 1
Next
Next
End Sub

davew
10-03-2012, 08:56 AM
Thanks GarysStudent- this has sorted it