+ Reply to Thread
Results 1 to 30 of 30

Is this type of copy even possible?

  1. #1
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Is this type of copy even possible?

    Trying to create a custom solution in Excel, and everybody in the office is stumped.

    The Excel document has three worksheets, called MASTER, DATA1, DATA2. All three worksheets have the same columns, in the same locations. Only thing different is DATA1 and DATA2 have different info listed in their rows.

    What we want to do is have the MASTER sheet display content from both DATA1 and DATA2. The trick is that data will be updated in DATA1 and DATA2 regularly, additional rows of content will be added over time.

    How do we get this type of content to properly display in the MASTER sheet? We need MASTER to be dynamic enough to "add" rows from DATA1 and DATA2, or even delete rows from either of those sheets...all without overwiting data from the other worksheet. We can "reserve" a number of rows in MASTER, but we'd like to have it list all the rows from one, then all the rows from the other, if at all possible.

    Help? Thank you!
    Last edited by klamen; 01-14-2010 at 11:24 AM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    If you put all the data into MASTER you could then make some of it appear in DATA1 and some in DATA2, just by having an additional column in MASTER

    May be more practicable in the long term.

    CC

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Is this type of copy even possible?

    You would need code to copy the data from each sheet to the master.Would you clear existing data from the incoming sheets or h=just keep adding them?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by royUK View Post
    You would need code to copy the data from each sheet to the master.Would you clear existing data from the incoming sheets or h=just keep adding them?
    The incoming sheets (DATA1 and DATA2) may have additional rows of content added over time, but the columns would never change in any of the worksheets. We would like to be able to delete rows of content if needed, but if that is not possible, we can color fill the data fields on the DATA sheets so on the MASTER it shows that those row issues are already resolved.

    We can "copy/paste" to MASTER, but with 20 employees accessing DATA1 and DATA2 (and perhaps more worksheets), somebody's going to forget to manually update the MASTER worksheet.

  5. #5
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Here's the issue we have. If currently DATA1 has 5 rows of data, and DATA2 has 5 rows of data, it's easy to get that copied over automatically to MASTER. Problem is as soon as we add two rows of data to DATA1, it now has 7 rows of data to copy to MASTER. Now, this "overwrites" the data coming from DATA2 into MASTER, and none of it shows up.

    So, is there a way to automatically place an empty row in MASTER, that keeps the info from DATA1 an DATA2 separated? So if DATA1 has more rows added, it will simply go to MASTER (no matter how many rows are there), the "buffer empty row" is there, and then the data from DATA2 goes into MASTER? This would keep the data visible on Master, but not allow for additional rows in the DATA sheets to overwrite the other.

  6. #6
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    Quote Originally Posted by royUK View Post
    You would need code to copy the data from each sheet to the master.
    Au contraire, my dear Roy.

    We could create a query to reference the other tables (we would need to name them) and use union (or union all) to append the two sheets' worth of data...

    Are you willing to try this, klamen? It takes a little while to explain and I don't want to waste my time!

    CC

  7. #7
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    I'm willing to try anything at this point, CC 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 Thanks!

    By the way, I've got a test file with what I'm trying to do...just can't figure out how to get it attached here on my forum postings.

    EDIT: UPLOADED SAMPLE XLSX file
    Attached Files Attached Files
    Last edited by teylyn; 01-22-2010 at 05:39 PM. Reason: quote removed

  8. #8
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    When you're editing a post, click "go advanced" and underneath the main edit window is a "manage attachments" area - use that. If you upload a nice simple example I will be able to walk you through the steps much more easily.

    CC

  9. #9
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Thanks, edited my response above with the test document. All I want is to get everything from DATA 1 and 2 (and other future additional sheets) into MASTER, no matter how many rows there are in each of the sheets

    I do appreciate your helping me along!
    Last edited by teylyn; 01-22-2010 at 05:39 PM. Reason: quote removed

  10. #10
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Is this type of copy even possible?

    Quote Originally Posted by Cheeky Charlie View Post
    Au contraire, my dear Roy.

    We could create a query to reference the other tables (we would need to name them) and use union (or union all) to append the two sheets' worth of data...

    Are you willing to try this, klamen? It takes a little while to explain and I don't want to waste my time!

    CC
    I would also be interested in learning this.

    Andrew

  11. #11
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Still looking for ideas.

  12. #12
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Smile Re: Is this type of copy even possible?

    Maybe it's not the best option but it works. I've uploaded workbook.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is this type of copy even possible?

    Contaminated,

    In these declarations,
    Please Login or Register  to view this content.
    ... everything except the last variable on each line is a Variant. What you want is
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  14. #14
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    shg
    I'm really new in VBA and I can make a mistake. So thank you for correction.
    Briefely for the future: I must dim worksheet and range separately? Right.

    I don't want to arque, but the code works, so why to change smth?

    I know I'm wrong here by asking that, but I just want to understand

    thx in advance...

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is this type of copy even possible?

    You must specify the data type for each variable, else the variable defaults to a Variant.
    I don't want to arque, but the code works, so why to change smth?
    You don't have to declare variables at all; it's just good practice. It enables VBA to detect type mismatches, and avoids unnecessary errors.

  16. #16
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    Sorry everyone, I have been distracted by my day-job, how irritating.

    Let's give this a try...

  17. #17
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    aaaaaa
    Thank u very much for explanation....
    Last edited by contaminated; 01-15-2010 at 01:20 PM.

  18. #18
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    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:
    Please Login or Register  to view this content.
    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:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    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]:
    Please Login or Register  to view this content.
    NB, trying to clarify this, we have:
    Please Login or Register  to view this content.
    we break it apart:
    Please Login or Register  to view this content.
    and stick it back together:
    Please Login or Register  to view this content.
    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:
    Please Login or Register  to view this content.
    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.
    Last edited by Cheeky Charlie; 01-15-2010 at 02:56 PM. Reason: Trying to clarify the SQL editing + PS

  19. #19
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Cheeky Charlie, I appreciate your taking the time to walk me through this. We sat down and tried to implement the instructions with a little more in depth worksheet, and it all pulled together fine, but just doesn't want to input data into the Master when it's entered under the other tabs.

    Any chance you can take a look at the attached, and see what we screwed up? Thanks!
    Attached Files Attached Files

  20. #20
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    Hi

    Try this code pls

    Please Login or Register  to view this content.
    Last edited by contaminated; 01-18-2010 at 01:12 PM.

  21. #21
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by contaminated View Post
    Hi

    Try this code pls
    Try it...how? Before/after/instead of/something else of the code already sent by Cheeky? I tried before, after, and in replace of...nothing happens to the master when data is entered.

  22. #22
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    Take a look
    Attached Files Attached Files
    Last edited by contaminated; 01-18-2010 at 01:39 PM.

  23. #23
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by contaminated View Post
    Take a look
    Vusal....getting closer I see the header row is now missing off the master...can that be put back in, and the data imported to a lower row to start? Thanks.

  24. #24
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    replace this with previous one. It works for me. I can upload a sample if u want
    Please Login or Register  to view this content.
    and the data imported to a lower row to start?
    really didnt understand.....
    Last edited by contaminated; 01-18-2010 at 01:54 PM.

  25. #25
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by contaminated View Post
    replace this with previous one. It works for me. I can upload a sample if u want
    Sure, if you could upload a sample, that would be great.

    I took your last version, and filled it out with 5 rows of data for each of the worksheets. Not all the data is carrying over though...still trying to figure that part out as well. Think it may have something to do with the column headings, or some of the same data being in different rows.

  26. #26
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    I took at your last sample. I put in 5 rows of data in each tab. The last row of data isn't coming through to the master...must be getting overwritten by the next round of data from the next tab. I'll attach an example, you'll see that data for #5 of each tab isn't coming through. Thoughts?
    Attached Files Attached Files
    Last edited by klamen; 01-18-2010 at 02:23 PM.

  27. #27
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by contaminated View Post



    really didnt understand.....
    Sorry. When the master has data in it, the top row no longer shows the column headings. Intead, it looks like the data from the first tab is populating over the top of the column headings Wondered if there was a way to get the data not to overwrite the top row of the master worksheet? It's easily seen in the example I just loaded a minute ago. Thanks!

  28. #28
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Is this type of copy even possible?

    Hi sorry for mistake.
    I fixed this bug, I didn't add 1 to iRng

    Please Login or Register  to view this content.
    THINK IT"S ALL OK NOW!!!
    Attached Files Attached Files
    Last edited by contaminated; 01-18-2010 at 02:45 PM.

  29. #29
    Registered User
    Join Date
    01-14-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Is this type of copy even possible?

    Quote Originally Posted by contaminated View Post
    Hi sorry for mistake.
    I fixed this bug, I didn't add 1 to iRng

    THINK IT"S ALL OK NOW!!!
    Excellent work, Contaminated! Looks great. I'm curious to see CC's idea too to finalization, as it seems to have a different path of development.

  30. #30
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Is this type of copy even possible?

    Hi there,

    I see the work you've put in to creating all the names etc. this must be frustrating - sorry, you're kind of a guinea pig, I've never seen done what we're trying here, it just struck me as a good idea...

    When I looked into it, here's what I found, theory:
    Microsoft Query is not very clever, or pretty, it's a bit of an appendix for MS, as Excel is pretty good at sourcing data on its own, and anyone sophisticated enough with data to be getting really fancy would probably find it makes more sense to use another package (no offence to anyone on the board) - Access possibly, but more likely MySQL or somesuch.
    When I tried to do the pulling apart/sticking together of the SQL which I illustrated in my first example, I noticed that the extension, .xlsm, was being appended to the filename (you'll notice in my examples above that there is no extension on the filename when the SQL syntax was created automatically - but then I saved the file as a 2003 xls before I started last time). This means that the filepath has an implied 'xlsm' table inside it - this concept should be fairly familiar to VBA people, the . is a bit like saying "and inside that" so the SQL structure shown above is basically "file.table.field"; when the .xlsm is included, MS Query tries to look inside the object 'xlsm' in the file (then if it got anywhere it would try to look for the tables then fields) - of course it can't find anything, there isn't any 'xlsm' entity inside your file. I would guess the reason the extension is put there is related to what I mentioned above, four-letter extensions are relatively new, definitely much newer than MS Query and I wonder if it hasn't been updated to accomodate four-letter extensions.

    And in practice:
    When I try manually removing the extensions, but still running off the xlsm, I can't get it to work. If I save the file as a 2003 xls, it does work. I'm going to go ahead and upload an example of that anyway (although anyone testing this would need to redefine the filepath embedded in the query, so it won't work "out of the box" but it should be fairly intuitive.

    Conclusion:
    This is quite a neat way of doing what you’re trying to do, but falls over on a bit of broken MS functionality. If you were willing to keep the file as an xls, which I feel is not too great a sacrifice, then you could employ my method. I would have to say that in terms of “good practice” my general rule of thumb is to avoid VBA where practical (it’s just so damn hard to make it error-proof, for example, your blank row on one of your uploaded sheets will break contaminated’s macro ), but I am pushing Excel and MS query so far here that it’s much more 50:50 than “VBA is bad”.

    More poignant conclusion:
    This is:
    1. Not the right package for doing data operations like this – Access would pop this out in moments. You mention earlier how MS didn’t think about users needing consolidation, I see your point, and I have my own grumbles with Excel, but I’d say you’re right on the edge of Excel’s design paradigm here, and so I wouldn’t expect it to be as polished as the main interface. An equivalent is the oft-quoted “I’m trying to write my essay in Excel and it won’t let me fit the whole thing into one cell, is there any way around this?”.
    2. The “wrong way round” for manipulating data. You have a ‘sub’ column in your master which correlates to each of your subsidiary sheets, if you simply inserted all the data into the master to start with, you could easily create filters and tables of that for each sub; even better you could create a single *pivot table* with the sub as a page field and have total flexibility of how to cut and view your data. I would recommend you try creating an mdb, just for experimentation - but I'm not going to go into detail here about how that would work - this is already quite long enough.

    Note to board:
    Not to get my own trumpet out, but I really love this idea, I’ve never seen it before, and, in the right situation, it works really bloody well. Shame about the compatibility problems

    CC
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1