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.