How to combine multiple columns in excel

Question

I'm using this formula that combines up to 3 columns in excel:

=INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98)),0))&" "&INDIRECT("B"&MOD(ROUNDUP((ROW())/COUNTA($C$1:$C$98),0)-1,COUNTA($B$1:$B$98))+1)&" "&INDIRECT("C"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($C$1:$C$98))+1)

It will basically combine the content of three columns like this:

Works great with 3 columns, however, I tried modifying the formula to support a fourth column (and perhaps more), but it's not combining the words properly. I was wondering if someone could show me how to modify it to make it work with 4 and maybe more columns.

Thanks!

Answer 1

If you break down the formula, you'll notice a pattern involving calls to INDIRECT, so removing the inner formulas for each column you have something like this:

=INDIRECT("A"&XXX)
&" "&INDIRECT("B"&YYY)
&" "&INDIRECT("C"&ZZZ)

Where XXX, YYY, and ZZZ are the respective formulas for each column. Therefore, all you need to do is append an additional &" "&INDIRECT("D"&TTT) where TTT is the formula for the new 4th column.

Extending my answer, the function you are looking for looks like this:

=INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))&" "&INDIRECT("B"&ROUNDUP((ROW())/PRODUCT(COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))&" "&INDIRECT("C"&MOD(ROUNDUP((ROW())/COUNTA($D$1:$D$98),0)-1,COUNTA($C$1:$C$98))+1)&" "&INDIRECT("D"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($D$1:$D$98))+1)

Breaking it down, you'll notice that the last 3 INDIRECT calls are identical to your existing INDIRECT calls, except all the references are shifted by one character (ie, A became B, B became C and C became D). Now look at the new first INDIRECT call and compare it to the second one:

INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))
INDIRECT("B"&ROUNDUP((ROW())/PRODUCT(COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))

You should notice that they differ only in that the first line has an additional ,COUNTA($D$1:$D$98) code inside the PRODUCT function. Using these observations, you should be able to expand the formula to further columns, but as @Tim Biegeleisen said, you should look for a different approach if your intention is to expand the function.