Steps:
I know of two ways of inserting queries into Excel 03 [aside] I notice the new forum CSS has done away with people's xl version info - pity, maybe it will be recovered...[/aside] they are:
Data->Import External Data->Import Data...
and
Data->Import External Data->New Database Query...
The latter allows the use of some handy database functions, but requires named tables (where the former can alternatively use what I believe is the 'used range' of a sheet).
So, first step, define the tables, I would use dynamic named ranges, but unfortunately the query editor can't "see" calculated named ranges, so we must define the table absolutely, this means this solution will not work in every scenario, where it does work, however, it is quite ‘neat’. The reason this post took a while to return is that I have been experimenting with the sizes of the named ranges. Defining 100, 1,000, and 10,000 rows all take a few moments to return. Selecting a whole column range, or something like A1:H65535 totally kills my computer (i.e. nothing happens for more than 7x longer than it took to return 10,000 rows) I don’t quite understand this, but if you can be confident that 10,000 rows is all you’ll ever need (20,000 maximum combined) you should be fine. Bear with me, this will make a bit more sense in a minute.
So, select DATA1
xl03: insert->name->define
xl07: alt+i, n, d
Name:"Data1table" (or somesuch, I would recommend against naming it exactly as per the sheet, no spaces, and don't start it with a number)
Refers to:=$A$1:$H$10000
<Add> (you should see the sheet reference inserted, this is fine, if you click back in the "refers to" box you should see marching ants around A:H)
Now just replace the 1 in the name, and the 1 in the inserted sheet reference with 2s
<OK>
If you haven't already saved the xls(x) you will need to, as you need to reference it in the query, so it must exist somewhere on your computer (apart from RAM!). You don’t need to resave it in order for the referenced ranges to show up.
Now for the query, Delete the field headers on Master and select A1 (not strictly necessary but easier this way)
xl03: Data->Import External Data->New Database Query...
xl07: Alt+d,d,n
<Excel files>
[browse to this file]
<OK>
Click Data1table (the words, not the +, you want the whole thing)
Click the >
Click Data2table (the words, not the +, you want the whole thing)
Click the >
Next >
Warning about joining tables – OK (it assembles the query in a stupid way, but selecting the tables here writes a chunk of syntax for us…)
Click the SQL button under Format on the menu bar
You should see a large SQL select statement, we need to rewrite it to push the tables together (stack them one under the other instead of adjacent)
My SQL statement looks like this:
You can see that it first pulls all the columns through from table 1, then pulls all the columns through from table 2, let’s separate those queries:
Now we need to 'stack' them together, for which we use "UNION" [aside] UNION will deliver unique entries only, so identical rows between the two tables would not be extracted, UNION ALL would ensure duplicates were carreid through too - your choice, I suppose[/aside]:
NB, trying to clarify this, we have:
we break it apart:
and stick it back together:
Now OK
Query can’t be represented graphically (OK)
Close
Where do you want to put the data? A1
OK
Job done.
Mine has a blank row between the field headers and the data, I have no explanation for that.
Fundamentally, that is it, there’s one more tweak we can add to improve the user experience:
Right-click the MASTER sheet tab
View code
in the window that pops up, paste this:
This will refresh the query each time the sheet is activated, otherwise users will have to use data->refresh data to update the MASTER sheet.
Soooo, an alternative to the macro:
Only needs code for added usability
Not totally flexible (limited number of rows) although if there was more than 65k (or 1m in xl07) rows in total then a macro would bug out anyway, so it’s not significantly different.
...
And I quite like it.
Favourite post of the year so far :D
PS we can look at sorting and filtering later, if anyone asks, you'll notice it sorts by the first field.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by royUK
You would think this would be something very simple with a copy function, but guess nobody else at MS thinks consolidating info onto a master is important 
Re: Is this type of copy even possible?
Bookmarks