# concatenate join or merge data

dunc1234 18:58 19 May 2007
Locked

hi, i'm after a small program or script to join multiple columns of data to every combination, preferably that could work in excel, or if it can be explained in very simple terms, in access.

an example of what i want to achieve is as follows

in column A is 1,2,3
in column B is A,B,C
in column C is 9,7,3

i want to output
a19,a29,a39,b19,b29,b39,c19,c29,c39
a17,a27,a37,b17,b27,b37,c17,c27,c37
a13,a23,a33,b13,b23,b33,c13,c23,c33

i know it can be done with pasting formulae but the numbers of data is quite large as well as the variation as there are more than a dozen columns that need mixing in more than one combination.

the length of data in each column varies as well.

if anyone could help on this it would be appreciated, and i do realise the calculations will invole huge numbers, but hey isn't that what computers are good at :o)

VoG II 19:15 19 May 2007

I don't know if I could help or not because I don't understand how you get from your inputs to your outputs.

What are 'a19' and so on - are they alphanumeric codes or cell references?

MAJ 21:53 19 May 2007

Looks like dunc1234 wants to output all the dirfferent permutations of the three columns, A,B and C. He seems to have listed his outputs incorrectly though, if I'm correct, he should have put:

A19,A29,A39,B19,B29,B39,C19,C29,C39, etc. etc.

dunc1234 15:46 20 May 2007

hi
sorry for confusion but good news is i have managed to get a script which does the required job although it only works with two rather than three columns.
the numbers and letters probably confused what i was trying to say (me thinking i'm making life easier)

if column one had joe, fred , dave and column two has jones, smith bloggs

(i'll skip third column)
i wanted the output in a separate column to be
joe jones
joe smith
joe bloggs
fred jones
fred smith
fred bloggs
dave jones
dave smith
dave bloggs

hopefully thats explained it better, found the macro to achieve this which is;
Concatenate two columns with VBA loop

The easiest and most flexible way to concatenate two columns of text into a
list of all possible combinations is to do it via a VBA macro.

If, for example, you have the following spreadsheet:

A B C D
1 Alabama Hotel
3 Arizona
4 Texas
5 Washington

You could then run the following macro to concatenate each possible
combination of city and site in column D:
VBA Code:

Sub combine_columns()
Dim town As Variant
Dim site As Variant

Range("D1").Select 'Change as appropriate
For Each site In Range("B1:B2") 'Change as appropriate
For Each town In Range("A1:A5") 'Change as appropriate
ActiveCell.Formula = town & " " & site
ActiveCell.Offset(1, 0).Select
Next
Next
Range("D1").Select

End Sub

Try opening your VBA editor from Tools >> Macro >> Visual Basic Editor,
insert a new module by choosing Insert >> Module and paste the above code
in. The macro will now be available from Tools >> Macro >> Macros.
Posted by Russell Penlington on 20-Apr-2006
Confused
Works Great, But...
I use the concatenate function to help build out lengthy keyword lists, and
this macro works like a charm. However, it would be great if I didn't have
to edit the cell ranges in the macro each time I use it. Some times I have 3
items in column A and other times I have 30. Does anyone know of a way to
make the ranges in this macro more dynamic?
Posted by ProjectMatt on 29-Nov-2006
Happy
Dynamically get end of columns

We can dynamicallly get the end of a column to make the selections with a
modification to the VBA code such as:

VBA Code:
Range("A1", Range("A1").End(xlDown)).Select
Posted by Russell Penlington on 29-Nov-2006
Grateful
Thank You !
I added the new VBA code, and it works like a charm. Thanks again!

hope this of use to anyone else.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…