+ Reply to Thread
Results 1 to 13 of 13

Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

  1. #1
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Hi all,

    I'm really struggling with this one - I thought it would be easy going into it.

    I have a sheet with a data dump containing data on several railcars, taking up approximately 500 rows. Some railcars take up multiple rows, one row for each unique customer, and one railcar can have up to 5 customers' goods on it. What I need to do is get this information separated out onto monthly sheets, based on the departure date of the railcar. Every row of data in the dump has a column with the departure month I need in it, so that would make it seem easy. I have 12 sheets created one for each month, with the same headers as in the dump.

    The two tricky parts:
    1. The data in the dump is not sorted by railcar number, but the results in the monthly sheets need to be.
    2. I don't believe LOOKUPS will work because rows in the dump repeat railcar numbers because of the multiple customers, and each of those rows has to be brought over to the monthly sheets, sorted and subtotaled by railcar number.

    Finally, every week or so, I'm going to want to drop a new data dump in the dump sheet and need the monthly sheets to update dynamically. The new dump won't simply be rows added on to the same data as before, all the rows could be different. If this is solved most easily with a macro (i.e. drop the new data in the dump sheet, press a macro button and it populates the monthly sheets) I'm fine with that, but would need help with that code for sure!


    Thanks to anyone who can help.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Cross posted here:
    http://www.mrexcel.com/forum/excel-q...use-macro.html

    A message to forum cross posters, please read the following link:
    http://www.excelguru.ca/node/7
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Hi Nola,

    I believe most of what you want to do is based on creating a vlookup to determine the month. In that case, you would probably want a vlookup with an approximate result. or a vlookup :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . If that is possible, then assuming the mastersheet is always complete. Delete the monthly sheets each time the macro runs and populate it with the all of the data, and set up the macro to sort the data by cars, or customers or both. I find name ranges to be extremely helpful. For now, I have attached an example of how to get months from calendar dates with fiscal month. Without a workbook to go by I would be shooting in the dark to write a macro.
    Here is the code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    @Stanley - understood on the cross-posting and will take heed on all future posts.

    @Greg, thanks so much for the help thus far. On Monday when I return to my desk, I will upload a file with the source data as well as my expected results, to help you help me

    Thanks again.
    Until Monday,
    Nola

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Nola111,

    In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

  6. #6
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Hi all,

    I have uploaded a workbook to help you all help me.

    The SAPDUMP sheet is the dump that comes from our system. I have included an Indirect sheet in case someone were to choose to delete a couple rows from the dump after deciding a car is not needed in the analysis. However, the Indirect sheet may not be needed if this is going to be accomplished with running a macro. Any sorting that needs to be done on the SAPDUMP data would need to be done inside the macro, rather than before running the macro.

    I've included 2 months, Jan and Feb, as sheets showing the example of the output format I've been asked to provide to management.

    Some points to consider for your reference:

    - The output order should be chronological by Departure Date. For any railcars with same departure date, the next sort should then be by railcar number.
    - For any railcar number that has more than one Drop Date, the rows of that railcar should be chronological by Drop Date - if all drop dates are the same on any one railcar, sort order is irrelevant and can be anything.

    For me the tricky parts are the subtotaling on the results sheets (because the subtotals could fall onto any row), as well as the fact that any one railcar can have any number of rows on the SAPDUMP sheet, and not in any order. And perhaps the trickiest with regard to that point - many railcars have multiple rows that should show as just one row on the results pages (see railcars 3-13, 5-13 7-13 as examples). In these examples, all pieces of data in the multiples rows of the respective railcar on the dump page are identical except for the Loaded Weight amount, so perhaps not TOO tricky.

    Ideally, management can just drop data into the SAPDUMP sheet, press the macro button, and the monthly reports are generated.

    I would GREATLY appreciate any help I can get from you guys on this.

    DISCLOSURE: CROSS-POSTED ON MR EXCEL!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    I would want to know whether you were wanting this by calendar month or is it by fiscal month. Also, I have to be out of town this week so I will not be able to support your effort.

  8. #8
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Quote Originally Posted by greggpetersen75 View Post
    I would want to know whether you were wanting this by calendar month or is it by fiscal month. Also, I have to be out of town this week so I will not be able to support your effort.
    Definitely Calendar Month. Thanks for you interest in any case, Greg!

  9. #9
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Hi, as a super user of SAP I will note to you a couple of things that might facilitate your reporting in SAP. You can select multiple sort orders in SAP. If you go to Choose layout. Select the tab of Sort Order and sort it by the criteria above. In addition, SAP allows you to create Sub-Totals. For example, if you high light the weight button K&T hit the sum button while highlighted. Then choose the sub-total button, after highlighting Car Name, it will allow you to summarize the delivery by that car name by month.

  10. #10
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Quote Originally Posted by greggpetersen75 View Post
    Hi, as a super user of SAP I will note to you a couple of things that might facilitate your reporting in SAP. You can select multiple sort orders in SAP. If you go to Choose layout. Select the tab of Sort Order and sort it by the criteria above. In addition, SAP allows you to create Sub-Totals. For example, if you high light the weight button K&T hit the sum button while highlighted. Then choose the sub-total button, after highlighting Car Name, it will allow you to summarize the delivery by that car name by month.
    Greg - logical minds think alike. I already tried this avenue with management Some old-school folks just aren't willing to deviate from the exact format (and mentality) that they're used to. On top of all that, we're constantly being advised against using different variants and layouts in SAP b/c management and our auditors want everyone using the exact same data dumps out of SAP.

    Trust me, I beat this one to death already. They want the spreadsheet. Thanks for the info though, validates for me that I'm thinking of the better approach as well!

  11. #11
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Thank you for my Dilbert moment of the day. Talk about the tail wagging the dog and nothing's impossible for those who don't have to do it.

  12. #12
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Quote Originally Posted by greggpetersen75 View Post
    Thank you for my Dilbert moment of the day. Talk about the tail wagging the dog and nothing's impossible for those who don't have to do it.
    Ha! Truth!

  13. #13
    Forum Contributor
    Join Date
    08-22-2013
    Location
    USA
    MS-Off Ver
    MS Office for Office 365 (Latest - 32-bit)
    Posts
    107

    Re: Populate 12 Sheets with data from a dynamic dump of the entire year - use a macro?

    Quote Originally Posted by stanleydgromjr View Post
    Nola111,

    In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.
    BUMP.

    Any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Use info from a dump to populate another sheet
    By Skiwan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2012, 04:14 PM
  2. 1018 sheets compiled into 1 master data dump
    By ryandurick in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2012, 09:32 AM
  3. Macro to dump data onto sheet
    By jw01 in forum Excel General
    Replies: 12
    Last Post: 08-24-2011, 06:49 PM
  4. Error with Data Dump Macro
    By maani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2009, 09:34 AM
  5. Sumif macro for raw data dump
    By Popa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2008, 03:32 PM

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