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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

GCM

New Member
Joined
Nov 16, 2008
Messages
23
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Doesnt seem to work past row 18, though I dont have another solution :(

I believe there should be 27 generated rows
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
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)
 

mark-davis

New Member
Joined
Dec 10, 2009
Messages
13

ADVERTISEMENT

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.
 

mark-davis

New Member
Joined
Dec 10, 2009
Messages
13

ADVERTISEMENT

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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
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)?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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
Top