How to combine 15 minute data files into 1 hour total file?

[enduser0001]

Let’s say I pull data files like this…
SYSGRPPERF.051007-1215.csv
SYSGRPPERF.051007-1230.csv
SYSGRPPERF.051007-1245.csv
SYSGRPPERF.051007-1300.csv

There are 4 data files per hour in15 minute increments
I want to pull 168 hours from the entire week.
That means there would be 672 files that I would like to compress into 168 hours in one report.

The code I use currently is this… It opens and combines all workbooks into one work sheet. Its not great code because it takes time, but it works and I haven’t messed with it yet.
I got this from www.VBAEXPRESS.com

Option Explicit
  
Sub CombineFiles()
      
    Dim Path            As String
    Dim Filename        As String
    Dim Wkb             As Workbook
    Dim ws              As Worksheet
      
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "C:\Documents and Settings\JTimko\Desktop\Weekly Data\sysgrpperffiles" 'Change as needed
    Filename = Dir(Path & "\*.csv", vbNormal)
    Do Until Filename = ""
        Set Wkb = Workbooks.Open(Filename:=Path & "\" & Filename)
        For Each ws In Wkb.Worksheets
            ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next ws
        Wkb.Close False
        Filename = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
      
Sheets("Sheet1").Select


Dim r As Long

'Turn screen updates off to make things look nicer
Application.ScreenUpdating = False

'Delete everything in Master sheet, from row 2 downwards
ActiveSheet.UsedRange.Offset(1).Clear

'Loop through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Do this for all sheets except the Master sheet
If ws.Name<> ActiveSheet.Name Then
'Copy from 4th row and downwards
ws.UsedRange.Offset(0, 0).Copy
'Paste values to Master sheet, below last used row
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial xlPasteValues
End If
Next ws

'Remove completely empty rows
For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r

'Select cell A1
Range("A1").Select

'Turn after-copy-blinking off
Application.CutCopyMode = False

'Turn screen updates back on
Application.ScreenUpdating = True

    
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name<> "Sheet1" And ws.Name<> "" Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

MGW TEMPLATE BETA.xls
	A	B	C	D	E	F	G	H	I	J
1	STARTTIMESTAMP	STOPTIMESTAMP	SYSGRPNUM	WMGNODENUM	NUMBEROFRESOURCES	INCOMINGUSAGESEC	OUTGOINGUSAGESEC	INSERVICERESOURCES	GROUPNAME	MSFNODENAME
2	10/7/05 16:00	10/7/05 16:15	212	3	3058	949802	140245	3058	RCMDKY05BSCE3	RCMDKYMGW03
3	10/7/05 16:15	10/7/05 16:30	212	3	3058	939902	169738	3058	RCMDKY05BSCE3	RCMDKYMGW03
4	10/7/05 16:30	10/7/05 16:45	212	3	3058	763918	333494	3058	RCMDKY05BSCE3	RCMDKYMGW03
5	10/7/05 16:45	10/7/05 17:00	212	3	3058	840419	231234	3058	RCMDKY05BSCE3	RCMDKYMGW03
6	10/7/05 17:00	10/7/05 17:15	212	3	3058	958473	184489	3058	RCMDKY05BSCE3	RCMDKYMGW03
7	10/7/05 17:15	10/7/05 17:30	212	3	3058	1010283	102654	3058	RCMDKY05BSCE3	RCMDKYMGW03
8	10/7/05 17:30	10/7/05 17:45	212	3	3058	964563	165184	3058	RCMDKY05BSCE3	RCMDKYMGW03
9	10/7/05 17:45	10/7/05 18:00	212	3	3058	739469	328441	3058	RCMDKY05BSCE3	RCMDKYMGW03
Sheet1

What I want to do is this; make each 15 minute to show as being part of one hour.
10/7/05 16:00 10/7/05 16:15
10/7/05 16:15 10/7/05 16:30
10/7/05 16:30 10/7/05 16:45
10/7/05 16:45 10/7/05 17:00

I want 1 ro showing 10/07/05 1600 and have columns F and Columns G be summed for the given hour.
for example colum F should have one entry for 1600 hours showing 3494041 seconds and column G 8474711 seconds

How would that be done???

Edited by Von Pookie

 

[Tazguy37]

Re: How to combine 15 minute data files into 1 hour total fi

What I want to do is this; make each 15 minute to show as being part of one hour.
10/7/05 16:00 10/7/05 16:15
10/7/05 16:15 10/7/05 16:30
10/7/05 16:30 10/7/05 16:45
10/7/05 16:45 10/7/05 17:00

I want 1 ro showing 10/07/05 1600 and have columns F and Columns G be summed for the given hour.
for example colum F should have one entry for 1600 hours showing 3494041 seconds and column G 8474711 seconds

How would that be done???

I'd add a column showing the hour for each time, and use Data Subtotals to add those columns for each hour. HTH

 

[enduser0001]

Re: How to combine 15 minute data files into 1 hour total fi

What I want to do is this; make each 15 minute to show as being part of one hour.
10/7/05 16:00 10/7/05 16:15
10/7/05 16:15 10/7/05 16:30
10/7/05 16:30 10/7/05 16:45
10/7/05 16:45 10/7/05 17:00

I want 1 ro showing 10/07/05 1600 and have columns F and Columns G be summed for the given hour.
for example colum F should have one entry for 1600 hours showing 3494041 seconds and column G 8474711 seconds

How would that be done???

I'd add a column showing the hour for each time, and use Data Subtotals to add those columns for each hour. HTH

I tried that and it works partially. Excel still sees the individual cells as 4:15, PM and so on even though you can format it as hour 16.
I still cannot get real totals for an hour based on 4, 15 minute increments.
Like I said I need a total of seconds of use in a whole hour and I need to know the ID numbers corresponding each hour. In other words the other columns need not be added up.