Combining Work Sheets/MS Query

  • Thread starter Thread starter Mark McWilliams
  • Start date Start date
M

Mark McWilliams

I have an Excel file that has two worksheets for sales data. The first work
sheet has surpassed 65536 lines in Excel 2003 and I've continued the data
into the second worksheet. I've done some Google searches and they said
theirs a way to combine two work sheets using MS Query and then you can run
a pivot table against this data. I can't find any web site that explains
how to do what I'm wanting to do exactly and I've messed around with MS
Query a little bit and can't figure out how to combine the two worksheets.

Has anyone done this and can explain to me what I need to do or can direct
me to a good web site?

Thanks.........Mark
 
OK...Here's what you do.

1)Make sure both of your worksheets have column headings and Named Ranges
(example: rngConsol1, rngConsol2) that include all of the data you want to
consolidate, then save the workbook and close it.

2)In a new workbook....
Data>Import External Data>New Database Query
-The data source is Excel Files
-Navigate to your workbook and select BOTH range names
-MS Query will complain that it can't joint the tables...Ignore and continue

At the Query window, click the SQL button.

The query will look something like this:

SELECT
rngConsol1.PhoneNum,
rngConsol1.Name,
rngConsol1.Addr,
rngConsol2.PhoneNum,
rngConsol2.Name,
rngConsol2.Addr
FROM
`C:\Excel Stuff\ForumHelp\PhoneStuff`.rngConsol1 rngConsol1,
`C:\Excel Stuff\ForumHelp\PhoneStuff`.rngConsol2 rngConsol2

You need to change it to look like this:

SELECT
rngConsol1.PhoneNum,
rngConsol1.Name,
rngConsol1.Addr
FROM
`C:\Excel Stuff\ForumHelp\PhoneStuff`.rngConsol1 rngConsol1
UNION ALL
SELECT
rngConsol2.PhoneNum,
rngConsol2.Name,
rngConsol2.Addr
FROM
`C:\Excel Stuff\ForumHelp\PhoneStuff`.rngConsol2 rngConsol2


Note the "UNION ALL" Statement I snuck in there.

Click the OK button to see the results.
If everything looks OK....Save the query (File>Save). Accept the default
location, because Excel will always look there for the query.

Now, you can cancel without returning data (File>Cancel and return to Excel)

That was the hard part.

The easy part is creating the Pivot Table
Data>Pivot Table
-External Data Source [Next]
-Get Data>Queries
-Select you query...and proceed as usual.

I hope that helps. Let me know if you run into any snags.

Regards,
Ron
 
Back
Top