concatenate with arrays

  • Thread starter Thread starter hande
  • Start date Start date
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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top