# concatenate with arrays

H

#### hande

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

A

#### Ashish Mathur

Hi,

Thank you for sharing this. Is there a source you can guide us to for
learning more on MS Query and the kind of problems it can solve.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

B

L

#### Lars-Åke Aspelin

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

A

#### Ashish Mathur

Hi,

"By way of MS Query you can also point a pivot table at an arbitrarily large
data source, say 1 million rows, and still work with the data in Excel
2003." - What exactly do you mean by this? How does this work?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com