concatenate with arrays
 

On Fri, 31 Jul 2009 15:00:01 -0700, hande
wrote:

Hello,

I want to porduce a list from two arrays.


A 1
B 2
C 3

I want the result to be
A1
A2
A3
B1
B2
B3
C1
C2
C3

I really have no clue.

Thanks

If your two arrays are in column A and B starting on row 1 and with a
blank cell below the data, you may try this formula in cell C1

=IF(ROW()(MATCH("_","_"&A$1:A$100,0)-1)*(MATCH("_","_"&B$1:B$100,0)-1),"",
INDEX(A$1:A$100,(ROW()-1)/(MATCH("_","_"&B$1:B$100,0)-1)+1)&
INDEX(B$1:B$100,MOD(ROW()-1,(MATCH("_","_"&B$1:B$100,0)-1))+1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula down as far as needed.

Hope this helps / Lars-Åke