+ Reply to Thread
Results 1 to 12 of 12

Inefficient Data Compilation

  1. #1
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Exclamation Inefficient Data Compilation

    Hi, I currently have 3 spreadsheets that i would like combined into 1 master sheet.

    There are two columns with data.
    These are Assessed Object and Value on Day X.

    I would like to be able to have all columns on the master sheet filled out.
    This means i would need the data from sheet1,sheet2 and sheet3. First off, how would I be able to get the individual objects data to match up on the master sheet without having to look for and select that one particular object? I know there must be a more effecient way where i can pick all objects with their corresponding values and excel sorts them out appropriately. How would I do that???

    Due to the fact that objects may be assessed one day and not the next there will be lots of "gaps" in the data. I would like to put a zero value in a blank cell when that happens. What I mean by this is on Day 3 some Assesed objects will not be the same as those on Day 1 (or Day 2) while some will be the same but will have a different Value on that Day.

    For example, car is an object that has been assessed all three days so there are no problems there. But farm has only been assessed on Day 2so I would like an automatic zero value for the days where it has not been assessed. How do i do this???

    Also, for objects that have appeared for the first time (such as bread on Day 3), I need to have the previous day values at zero. How would i go about getting excel to automatically do that???

    Normally I would copy and paste the data into the master sheet but im dealing with thousands of objects so thats out of the question. Thats why I would like to learn to compile this data more efficiently.

    Attached is an example excel file with the 3 spreadsheets and master sheet to illustrate what im trying to accomplish. Hoefully this can help you help me.

    Thankyou in advance
    Attached Files Attached Files
    Last edited by wowzers; 06-11-2009 at 01:32 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inefficient Data Compilation

    Give this little macro a try:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 06-06-2009 at 02:18 PM. Reason: Sheet removed...see below for latest version
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Exclamation Re: Inefficient Data Compilation

    Quote Originally Posted by JBeaucaire View Post
    Give this little macro a try:
    Please Login or Register  to view this content.
    Hi, JBeaucaire, thanks for the macro! I looked it over and it does what I want except there are a couple of problems....some items such as bread and milk that first appear on day 3 don't show up on the master list at all.

    Also, columns E and I are populated with random objects??
    I appreciate the help so far but cannot figure out VBA for the life of me.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inefficient Data Compilation

    Try not to use the QUOTE button unless you're quoting a specific part of my previous post. QUOTING the whole thing for no reason just makes the thread longer for no reason. Just use the QUICK-REPLY button. (you can even edit the previous post to remove all the extra...)

    ==========
    You're right. It is misbehaving. I apologize for not spotting that. I changed the order in which things are being done and it seems to have cleared that up.
    Please Login or Register  to view this content.
    (posts above cleared of bad sheets/code)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Inefficient Data Compilation

    Wow, thanks for the macro buddy, its working perfectly!

    Point taken about using the qoute button.

    I had one more question about this macro.After I compile the data into a master sheet, i will need to be able to compile additional data every day. Will this macro be sufficient for this???

    What i mean is once i have the mastersheet compiled, i will need to compile a sheet onto the master sheet every day....so do i just run the macro?? (because i dont really understand the code, is it pulling from the sheets?, If i had a file with only the master sheet and 1 additional sheet would it still work??)

    thanks for all you do

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inefficient Data Compilation

    First, the macro creates a flat report, no formulas exist in the report. So you can copy that report to anywhere and it will remain accurate.

    Each time you run it, it asks if you want to clear the old report and make a new. You've tried it out, yes?

    This macro was designed to your original specs/question. So it wasn't designed to count sheets or create columns, it fills out the report you designed. Currently it expects there to be 3 sheets named Sheet1, Sheet2 and Sheet3. We can edit those sheet names easily enough.

    The macro currently works fine on fewer sheets of data, just leave the unneeded sheets blank (but still there) and it will fill in zeros in your existing report, as you originally specified.

  7. #7
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Arrow Re: Inefficient Data Compilation

    yes, i tinkered with the macro quite a bit this afternoon and figured it needed three sheets. My day-to-day uses of the macro would only require 1 new sheet for that day to be compiled with the master sheet.

    For example,

    On day 1- house, car and boat are assessed....so only 1 sheet (day1 sheet) is used and this automatically becomes master sheet as i will record all other subsequent days values onto this sheet.

    The next day
    On day 2- house, car, and bike are assessed....now there would be 2 sheets (day2 sheet and master sheet). The values for house and car would have 2-days worth of data. But bike, since its a new entry would have an automatic zero for the first days data and would have its day2 data inputted. And since there is no data for boat, it too would have a zero value for day 2. The day2 sheet data would be accumulated with the master sheet. At the end of the day, all data would be found on the master sheet (which now houses day1 and day2 data).

    the next day
    On day 3- house, boat and farm are assessed....so house would have three days worth of data. boat would have its day 1 data, a zero for day 2 (since it was not assessed on this day), and its day 3 data. Farm, since its a new entry would have a zero for day 1, a zero for day 2, and its day 3 data. car and bike, which were not assessed on this day, would have a zero value for day three. Again, the The day3 sheet data would then be accumulated with the master sheet which contains all accumulated data from previous days (in this case, day1 and day2 data).At the end of the day, all data would now be found on this master sheet (which now houses day1, day2 and day3 data).

    On all subsequent days, if there is a new object entry, a zero would need to be automatically entered for all previous days (since the first day of data entry). This is why i made an example with 3 sheets on it, to illustrate this point.

    So, in reality, i need a macro that would be able to integrate 1 new sheet a day. So only 1 sheet and a master sheet.

    Phew! Im not a quick typer so that took a while, I hope ive made it clear as possible and hopefully this macro I have can be modded??

    Thanks

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inefficient Data Compilation

    I'm pretty sure 95% of that code goes in the trash then. Not even remotely the same project.

    I won't be able to look at that this weekend at all, sorry.

  9. #9
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Inefficient Data Compilation

    Ok, thanks for your help

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inefficient Data Compilation

    Be sure to read the forum rules (How To ... "Forum Rules" in the menu bar across the top) and include links here in this thread to other places you've posted the same question.

  11. #11
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Inefficient Data Compilation

    Ok, I have also posted this project on another excel forum as time is of the essence.

    Here is the link

    http://www.mrexcel.com/forum/showthread.php?t=394894


    Thanks for the heads up!

  12. #12
    Registered User
    Join Date
    06-05-2009
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Inefficient Data Compilation

    Does anyone know where I may be able to get this solved???? Like point me to a site where I can pay to get this done??? thankyou

+ 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