Concatenation looping problem

mark-davis

New Member
Joined
Dec 10, 2009
Messages
13
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

The following formula works, though its a bit long and complicated:

=CONCATENATE( INDIRECT("A"&INT((ROW()+5)/6)), " ",INDIRECT("B"&MOD(INT((ROW()-1)/3),3)+1), " ",INDIRECT("C"&MOD(ROW()-1,3)+1) )

Good Luck
 
Upvote 0
Doesnt seem to work past row 18, though I dont have another solution :(

I believe there should be 27 generated rows
 
Upvote 0
Put this in D1 and copy it down to D27...

Code:
=INDIRECT("A"&INT((ROW(1:1)-1)/9)+1) & " " & INDIRECT("B"&MOD(INT((ROW(1:1)-1)/3),3)+1) & " " & INDIRECT("C" & MOD(ROW(1:1)-1,3)+1)
 
Upvote 0
AlphaFrog,

This works! Question: If the length of each column varies, how would I change the formula to accept all non-blank entries in every row?

Much appreciated.
 
Upvote 0
AlphaFrog,

Sorry to confuse you. Right now, this formula will concatenate 3 rows of data. In some instances, I will need to do well one hundred rows. I need to modify the formula to accommodate the variability in row count.
 
Upvote 0
Will each column have the same number of rows? (i.e. not the case where A has 10 rows, B 12 rows, C 15 rows)

Will the number of columns always be the same (i.e. 3)?
 
Upvote 0
I could do it with VBA, but it's beyond my ability to do all that with just a formula. I'm not saying it can't be done, but I don't know how.

I defer to the real experts on this one. I'm just faking it.
 
Upvote 0
If the conditions I described apply try this formula, dragged down as far as needed.

=CONCATENATE(INDEX(A:A, MOD((ROW(A1)-1), COUNTA(A:A))+1, 1)," ",INDEX(B:B, MOD(INT((ROW(E1)-1)/COUNTA(A:A)), COUNTA(A:A))+1, 1)," ",INDEX(C:C, MOD(INT((ROW(G1)-1)/(COUNTA(A:A)^2)), COUNTA(A:A))+1, 1))

I'm not sure if I have columns A and C cycling the way you want, put replacing the first argument of INDEX and re-arranging the argument of CONCATENATE will fix that.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top