concatenate with arrays

[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

 

[smartin]

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

AFAIK, this is referred to as obtaining the Cartesian product. Here's
the way I know how to do it. This uses MSQUERY, which doesn't get a lot
of press but is very useful for manipulation of data (Excel and
otherwise) using SQL. You don't actually need to know SQL to do this
though.

Set up a little worksheet like this cornered at A1:

First Second
A 1
B 2
C 3

Create two named ranges like
First : refers to =Sheet1!$A$1:$A$4
Second : refers to =Sheet1!$B$1:$B$4

Save the workbook.

Data | Import External Data | New Database Query

In the "Choose Data Source" dialog, select "Excel Files*" | OK

Navigate to the file you saved. If everything is correct so far, you
will see "First" and "Second" in "Available tables and columns". Use the
chevron > to add each to the "Columns in your query". Next. The query
wizard complains that it can not join the tables, etc. OK. The query
editor will open and should display the kind of results you are looking
for. So, click File | Return Data to Microsoft Excel.

Now choose (click) a place to drop the query results, such as D1.

The results should look like

First Second
A 1
B 1
C 1
A 2
B 2
C 2
A 3
B 3
C 3

The results can be concatenated with the usual methods, e.g.,
=D2&E2

Hope you find this informative.

 

[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

 

[Bernd P]

Hello,

Array-enter into a sufficiently long area with 1 column:
=reshape(TRANSPOSE(A1:A3)&B1:B3)

My UDF reshape you can find here:
http://sulprobil.com/html/reshape.html

Regards,
Bernd

 

[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

 

[smartin]

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.

One site that comes to mind is Charlie Kyd's ExcelUser. He has a nice
step-by-step demonstration of setting up a query:

http://www.exceluser.com/explore/msquery1_1.htm

Other than that Google, and trial and error.

Since MS Query is a SQL tool, it provides a straightforward way to do
the kinds of things you would do in a database, such as join related
data sources, aggregate, filter on multiple criteria, etc. And of
course, you can generate a Cartesian product as discussed here.

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.

Have fun!

 

[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

 

[smartin]

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?

Hi Ashish,

Let's say you have an Access database with a table (or select query)
that returns over 1 million rows, and you want to make a pivot table out
of it. On a blank worksheet, fire up the pivot table wizard. In Step 1,
choose External data source. In Step 2, click Get Data... A Choose Data
Source dialog will open where you can pick the source ("MS Access
Database"). The rest should be self explanatory.

The beauty of it is Excel does not balk at the data set's row count. The
data goes to the pivot cache. This seems to be limited only by available
memory (I've used 2+ million rows for pivot table sources). Of course,
in Excel 2003 and prior you are limited to a 2^16 row x 2^8 column
workspace for the PT. By the way, I credit Mike Alexander for cluing me
in to the above functionality.

I guess this isn't really a function of MS Query per se at this point,
but you can use MS Query to intercept and manipulate the external data:
Once you have chosen the source file, table and fields, click Next a
couple times to navigate past the filter and sorting options. You will
find an option to "View data or edit query in Microsoft Query". From
here you can do pretty much anything you might want to do with SQL: join
in other tables, aggregate, etc.

One thing that does not seem to be supported when using the external
data widget for pivot tables is parameter queries (I only just realized
this).

Having said that, MS Query is quite useful for obtaining external data
using a parameter specified in your worksheet, and it can be configured
to update automatically.

I hope you found this helpful.