Concatenation looping problem

[mark-davis]

Hello All,

What a wonderful forum and I am sure this problem will get answered. Ideally, I'd like it solved with equations instead of VB.

I have 3 columns (column A, B, and C), each with 3 rows of data in Excel. I would like to concatenate each cell in A with each cell in columns B and C in the following manner:

cell d1--> concatenate(a1," ",b1," ",c1)
cell d2, concatenate a1+b1+c2
cell d3 concatenate a1+b1+c3
cell d4 concatenate a1+b2+c1
cell d5 concatenate a1+b2+c2...repeat according this sequence

Eventually, the last cell in column d will be concatenate a3+b3+c3.

 

[mark-davis]

Each column will have a different number of rows. For now, the maximum number of columns will be 3.

 

[mark-davis]

Mike,

This WORKS! Thank you all so much. Within an hour, solution is provided - WITHOUT VB.

 

[mikerickson]

One could construct an increadably complex, uneditable formula that would do this, but the VBA route sounds best.

Are you using 2008, that would make the macro un avaliable?

 

[mark-davis]

Office 2004.

I copied the formula all the way down the spreadsheet. After the expected concatenation, other values are being show. It would be nice to simply have the formula sense end of cell entries and just remain blank.

 

[mark-davis]

Whoops, spoke too fast. This works for 3x3 setup. If I place a value in a4, take a look at the undesirable concatenation that is starting to occur. (see attached image below):

 

[mikerickson]

The formula assumes that all of the columns have the same number of entries. The NoGoods are from concatinating the "" in B4 with other strings.

As I said in post #13, a VB solution would be the way to go.