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

"smartin" wrote in message
...
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

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 wrote:
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!

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" wrote in message
...
Ashish Mathur wrote:
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 wrote:
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.