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.

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:

=vlookup(c2,Sheet2!A:B,2,True)

. 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:

Sub MoveData()
Dim Ws As Worksheet
Dim xlCell As Excel.Range
Dim xlCell1 As Excel.Range
Dim strMonth As String

    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name = "Data" Then GoTo SkipDelete
        If Ws.Name = "FiscalCalendar" Then GoTo SkipDelete
        With Ws
            If .AutoFilterMode = True Then .AutoFilterMode = False
            .Cells.Delete
        End With
SkipDelete:
    Next Ws
        With Sheets("Data")
            If .AutoFilterMode = True Then .AutoFilterMode = False
        End With
     Call RangeName(Worksheets("Data"))
     Call RangeName(Worksheets("FiscalCalendar"))
     
    For Each xlCell In Range("MonthTbl")
        strMonth = xlCell
        With Sheets("Data")
        If .AutoFilterMode = True Then .AutoFilterMode = False
        End With
        Sheets("Data").Select
         Set xlCell1 = Range("Month").Find(What:=strMonth, LookIn:=xlValues, Lookat:=xlWhole)
        If Not xlCell1 Is Nothing Then
            Cells(1, 1).AutoFilter Field:=Range("Month").Column, Criteria1:=strMonth, Operator:=xlFilterValues
            Range("A1").CurrentRegion.Copy
            Sheets(strMonth).Range("A1").PasteSpecial xlPasteAll
        End If

       
    Next xlCell
End Sub
Private Sub RangeName(xlws As Worksheet)
    xlws.Select
    Application.DisplayAlerts = False
    Range("A1").CurrentRegion.Select
    Range("A1").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
    Application.DisplayAlerts = True
      
End Sub

@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

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!

Originally Posted by stanleydgromjr

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?

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.

Originally Posted by greggpetersen75

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!

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.

Originally Posted by greggpetersen75

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!

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.

Originally Posted by greggpetersen75

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!