Need VBA Macro to Parse XLSX File - Appreciate Your Help

[powerjak]

I'm an intermediate Excel user, but "don't know diddly" about creating VBA Macros for recurrent processes. I'd really appreciate some help from someone more advanced than I am to create a Macro to reduce the time it takes to process these files.

I frequently have to pre-process Hourly Sample data to reduce data volume and sync it up against other data at the same time intervals for deeper analysis. The raw data is provided to me in a XLSX file in a format as follows:

Date Time Value1 Value2 Value3 Value4 Value5 Value6 Value
9/2/2011 12:00:00 AM 50.21 42 47.38 79.77 84.74 1.16
9/2/2011 1:00:00 AM 49.1 42 47.39 78.76 83.69 0.83
9/2/2011 2:00:00 AM 48.18 42 47.38 78.39 83.52 0.62
9/2/2011 3:00:00 AM 48.29 42 47.38 78.39 83.52 0.62
9/2/2011 4:00:00 AM 47.8 42 47.38 78.39 83.44 0.61
9/2/2011 5:00:00 AM 47.08 42 47.38 78.39 82.3 0.41
9/2/2011 6:00:00 AM 47.39 42 47.38 76.68 81.42 0.12
9/2/2011 7:00:00 AM
9/2/2011 8:00:00 AM 46.35 42 47.38 74.73 79.85 -0.33
9/2/2011 9:00:00 AM 45.68 42 47.4 74.73 79.85 -0.36
9/2/2011 10:00:00 AM 48.79 42 48.1 74.85 80.14 -0.1
9/2/2011 11:00:00 AM 52.17 42 48.34 76.23 81.88 0.48
9/2/2011 12:00:00 PM 55.12 42 48.84 77.62 83.35 0.94
9/2/2011 1:00:00 PM 56.81 42 48.84 78.39 83.76 1.19
9/2/2011 2:00:00 PM 56.17 42 48.84 78.39 84.17 1.24
9/2/2011 3:00:00 PM 57.28 42 48.84 78.39 84.98 1.33
9/2/2011 4:00:00 PM 56.86 42 48.84 78.69 84.98 1.48
9/2/2011 5:00:00 PM 57.36 42 48.84 79.61 84.98 1.48
9/2/2011 6:00:00 PM 56.47 42 48.84 79.61 84.98 1.39
9/2/2011 7:00:00 PM 56.37 42 48.84 79.61 84.98 1.42
9/2/2011 8:00:00 PM 56.64 42 48.84 79.61 84.94 1.33
9/2/2011 9:00:00 PM 53.88 42 48.15 78.39 83.76 0.93
9/2/2011 10:00:00 PM 52.08 42 48.11 78.39 83.76 0.89
9/2/2011 11:00:00 PM 51.48 42 48.11 78.39 83.76 0.9
9/3/2011 12:00:00 AM 51.26 42 48.11 78.39 83.76 0.9
9/3/2011 1:00:00 AM 50.98 42 48.11 77.9 82.61 0.79
9/3/2011 2:00:00 AM 50.21 42 48.11 77.9 82.54 0.73
9/3/2011 3:00:00 AM 49.65 42 48.11 77.9 82.54 0.68
9/3/2011 4:00:00 AM 49.61 42 48.07 77.9 82.54 0.65
9/3/2011 5:00:00 AM 48.48 42 47.38 77.34 82.54 0.48
9/3/2011 6:00:00 AM 48.83 42 47.4 77.17 82.54 0.46
9/3/2011 7:00:00 AM 48.83 42 48.11 76.78 81.54 0.22
9/3/2011 8:00:00 AM 48.02 42 48.11 75.95 81.32 0.06
9/3/2011 9:00:00 AM 48.05 42 48.11 75.95 81.32 -0.06
9/3/2011 10:00:00 AM 49.8 42 48.11 75.95 81.32 0.2
9/3/2011 11:00:00 AM 50.91 42 48.11 75.95 82.05 0.59
9/3/2011 12:00:00 PM 51.83 42 48.11 75.95 82.54 0.59
9/3/2011 1:00:00 PM 53.34 42 48.18 76.74 82.54 0.66
9/3/2011 2:00:00 PM 53.94 42 48.84 77.17 82.54 0.8
9/3/2011 3:00:00 PM 54.11 42 48.84 77.17 82.54 0.87
9/3/2011 4:00:00 PM 54.91 42 48.84 77.17 83.21 0.94
9/3/2011 5:00:00 PM 53.91 42 48.84 77.17 83.76 0.9
9/3/2011 6:00:00 PM 54.52 42 48.84 77.17 83.76 0.92
9/3/2011 7:00:00 PM 53.38 42 48.49 77.17 83.56 0.86
9/3/2011 8:00:00 PM 50.54 42 48.11 77.17 82.54 0.47
9/3/2011 9:00:00 PM 49.9 42 48.11 77.17 81.87 0.26
9/3/2011 10:00:00 PM 48.4 42 48.11 77.17 81.32 0.2
9/3/2011 11:00:00 PM 47.76 42 47.5 77.17 81.32 0.13

Macro/VBA Parsing Process Specification
1. Worksheet to be processed has Header Row containing Column Titles. Must remain intact.
2. Number of Columns are variable. Example has Columns A through H, but could have many more. Must process all Columns until end of data.
3. Number of Rows are variable. Example has 48 Rows (excluding Header/Title Row), but could be many thousands of Rows total. Must process all Rows until end of data.
4. Each Row is an Hourly Data Sample of values taken Daily, On-the-Hour, beginning at 12:00:00 AM to 11:00:00 PM (24 Records/Rows per Day). File may start with any Calendar Date in format m/d/yyyy in Column A.
5. Column B contains the Hourly Time Stamp 12:00:00 AM through 11:00:00 PM for each Date in Column A.
6. Some data value cells may be blank although there are valid Date & Time values in Columns A & B. Such a Row must process correctly as long as the Date & Time values are valid. See Row 10 in "Example_BEFORE"
7. Rows to Extract have the Column B Time Stamps of 3:00:00 AM, 9:00:00 AM, 3:00:00 PM and 9:00:00 PM. (The Rows in "Example_BEFORE" that are highlighted in Yellow.)
8. DO NOT use Row Counting to capture every 6th Row because first Time Stamp may NOT be 12:00:00 AM, and Rows may be missing. Macro MUST process based on the actual Date & Time Values in Columns A & B.
9. Result of Macro Run leaves the original worksheet intact without change, and creates a NEW Worksheet in the Workbook containing each of the extracted Rows with their corresponding data.

Resulting New Worksheet After Macro Processing

Date Time Value1 Value2 Value3 Value4 Value5 Value6 Value
9/2/2011 3:00:00 AM 48.29 42 47.38 78.39 83.52 0.62
9/2/2011 9:00:00 AM 45.68 42 47.4 74.73 79.85 -0.36
9/2/2011 3:00:00 PM 57.28 42 48.84 78.39 84.98 1.33
9/2/2011 9:00:00 PM 53.88 42 48.15 78.39 83.76 0.93
9/3/2011 3:00:00 AM 49.65 42 48.11 77.9 82.54 0.68
9/3/2011 9:00:00 AM 48.05 42 48.11 75.95 81.32 -0.06
9/3/2011 3:00:00 PM 54.11 42 48.84 77.17 82.54 0.87
9/3/2011 9:00:00 PM 49.9 42 48.11 77.17 81.87 0.26
Can some lend a helping hand?

Thanks in Advance.

 

[hiker95]

powerjak,

We do not know the worksheet names, or, where the data resides in each worksheet.

Can we see your workbook with before and after worksheets?

See below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

 

[Andrew Poulsom]

It would be straightforward if the times were in 24 hour format. Is it possible to get the program that creates the Excel file to do that?

 

[venkat1926]

It is reckless of me to give a solution where MVPs have already given suggestions

I have copied the data in sheet 3 as follows.

the result is in sheet 2

logic is
1.clears sheet1 data
2..copies from sheet3
3. it finds the required times and copies those rows in sheet 2

copy the following data in sheet3

Excel Workbook
	A	B	C	D	E	F	G	H
1	date	time	value1	value2	value3	value4	value5	value6
2	9/2/2011	########	50.21	42	47.38	79.77	84.74	1.16
3	9/2/2011	########	49.1	42	47.39	78.76	83.69	0.83
4	9/2/2011	########	48.18	42	47.38	78.39	83.52	0.62
5	9/2/2011	########	48.29	42	47.38	78.39	83.52	0.62
6	9/2/2011	########	47.8	42	47.38	78.39	83.44	0.61
7	9/2/2011	########	47.08	42	47.38	78.39	82.3	0.41
8	9/2/2011	########	47.39	42	47.38	76.68	81.42	0.12
9	########	########
10	9/2/2011	########	46.35	42	47.38	74.73	79.85	-0.33
11	9/2/2011	########	45.68	42	47.4	74.73	79.85	-0.36
12	9/2/2011	########	48.79	42	48.1	74.85	80.14	-0.1
13	9/2/2011	########	52.17	42	48.34	76.23	81.88	0.48
14	9/2/2011	########	55.12	42	48.84	77.62	83.35	0.94
15	9/2/2011	########	56.81	42	48.84	78.39	83.76	1.19
16	9/2/2011	########	56.17	42	48.84	78.39	84.17	1.24
17	9/2/2011	########	57.28	42	48.84	78.39	84.98	1.33
18	9/2/2011	########	56.86	42	48.84	78.69	84.98	1.48
19	9/2/2011	########	57.36	42	48.84	79.61	84.98	1.48
20	9/2/2011	########	56.47	42	48.84	79.61	84.98	1.39
21	9/2/2011	########	56.37	42	48.84	79.61	84.98	1.42
22	9/2/2011	########	56.64	42	48.84	79.61	84.94	1.33
23	9/2/2011	########	53.88	42	48.15	78.39	83.76	0.93
24	9/2/2011	########	52.08	42	48.11	78.39	83.76	0.89
25	9/2/2011	########	51.48	42	48.11	78.39	83.76	0.9
26	9/3/2011	########	51.26	42	48.11	78.39	83.76	0.9
27	9/3/2011	########	50.98	42	48.11	77.9	82.61	0.79
28	9/3/2011	########	50.21	42	48.11	77.9	82.54	0.73
29	9/3/2011	########	49.65	42	48.11	77.9	82.54	0.68
30	9/3/2011	########	49.61	42	48.07	77.9	82.54	0.65
31	9/3/2011	########	48.48	42	47.38	77.34	82.54	0.48
32	9/3/2011	########	48.83	42	47.4	77.17	82.54	0.46
33	9/3/2011	########	48.83	42	48.11	76.78	81.54	0.22
34	9/3/2011	########	48.02	42	48.11	75.95	81.32	0.06
35	9/3/2011	########	48.05	42	48.11	75.95	81.32	-0.06
36	9/3/2011	########	49.8	42	48.11	75.95	81.32	0.2
37	9/3/2011	########	50.91	42	48.11	75.95	82.05	0.59
38	9/3/2011	########	51.83	42	48.11	75.95	82.54	0.59
39	9/3/2011	########	53.34	42	48.18	76.74	82.54	0.66
40	9/3/2011	########	53.94	42	48.84	77.17	82.54	0.8
41	9/3/2011	########	54.11	42	48.84	77.17	82.54	0.87
42	9/3/2011	########	54.91	42	48.84	77.17	83.21	0.94
43	9/3/2011	########	53.91	42	48.84	77.17	83.76	0.9
44	9/3/2011	########	54.52	42	48.84	77.17	83.76	0.92
45	9/3/2011	########	53.38	42	48.49	77.17	83.56	0.86
46	9/3/2011	########	50.54	42	48.11	77.17	82.54	0.47
47	9/3/2011	########	49.9	42	48.11	77.17	81.87	0.26
48	9/3/2011	########	48.4	42	48.11	77.17	81.32	0.2
49	9/3/2011	########	47.76	42	47.5	77.17	81.32	0.13
Sheet1

now try this macro "test"
and see whether it give you what you want.

Sub test()
Dim c As Range
Dim r As Range, ttime
Dim cfind As Range

Application.ScreenUpdating = False
undo
Worksheets("sheet1").Activate
For Each c In Range(Range("B2"), Range("B2").End(xlDown))
c = c.Offset(0, -1) + c
Next c

Set r = Range(Range("B2"), Range("B2").End(xlDown))
ttime = Range("B5").Value
'MsgBox ttime
Set cfind = r.Find(what:=ttime)
If Not cfind Is Nothing Then
'MsgBox cfind.Address
cfind.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
Do
ttime = ttime + 6 / 24
'MsgBox ttime
Set cfind = r.Find(what:=ttime)
If Not cfind Is Nothing Then
'MsgBox cfind.Address
cfind.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Else
GoTo exitsub
End If
Loop
exitsub:
Range("a1").EntireRow.Copy
Worksheets("sheet2").Activate
Range("a1").PasteSpecial
Range("B1").EntireColumn.AutoFit
Range("a1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Columns("B:B").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

End Sub

 

[venkat1926]

It is reckless of me to give a solution where MVPs have already given suggestions

I have copied the data in sheet 3 as follows.

the result is in sheet 2

logic is
1.clears sheet1 data
2..copies from sheet3
3. it finds the required times and copies those rows in sheet 2

copy the following data in sheet3

Excel Workbook
	A	B	C	D	E	F	G	H
1	date	time	value1	value2	value3	value4	value5	value6
2	9/2/2011	1/0/00 12:00 AM	50.21	42	47.38	79.77	84.74	1.16
3	9/2/2011	1/0/00 1:00 AM	49.1	42	47.39	78.76	83.69	0.83
4	9/2/2011	1/0/00 2:00 AM	48.18	42	47.38	78.39	83.52	0.62
5	9/2/2011	1/0/00 3:00 AM	48.29	42	47.38	78.39	83.52	0.62
6	9/2/2011	1/0/00 4:00 AM	47.8	42	47.38	78.39	83.44	0.61
7	9/2/2011	1/0/00 5:00 AM	47.08	42	47.38	78.39	82.3	0.41
8	9/2/2011	1/0/00 6:00 AM	47.39	42	47.38	76.68	81.42	0.12
9	########	1/0/00 7:00 AM
10	9/2/2011	1/0/00 8:00 AM	46.35	42	47.38	74.73	79.85	-0.33
11	9/2/2011	1/0/00 9:00 AM	45.68	42	47.4	74.73	79.85	-0.36
12	9/2/2011	1/0/00 10:00 AM	48.79	42	48.1	74.85	80.14	-0.1
13	9/2/2011	1/0/00 11:00 AM	52.17	42	48.34	76.23	81.88	0.48
14	9/2/2011	1/0/00 12:00 PM	55.12	42	48.84	77.62	83.35	0.94
15	9/2/2011	1/0/00 1:00 PM	56.81	42	48.84	78.39	83.76	1.19
16	9/2/2011	1/0/00 2:00 PM	56.17	42	48.84	78.39	84.17	1.24
17	9/2/2011	1/0/00 3:00 PM	57.28	42	48.84	78.39	84.98	1.33
18	9/2/2011	1/0/00 4:00 PM	56.86	42	48.84	78.69	84.98	1.48
19	9/2/2011	1/0/00 5:00 PM	57.36	42	48.84	79.61	84.98	1.48
20	9/2/2011	1/0/00 6:00 PM	56.47	42	48.84	79.61	84.98	1.39
21	9/2/2011	1/0/00 7:00 PM	56.37	42	48.84	79.61	84.98	1.42
22	9/2/2011	1/0/00 8:00 PM	56.64	42	48.84	79.61	84.94	1.33
23	9/2/2011	1/0/00 9:00 PM	53.88	42	48.15	78.39	83.76	0.93
24	9/2/2011	1/0/00 10:00 PM	52.08	42	48.11	78.39	83.76	0.89
25	9/2/2011	1/0/00 11:00 PM	51.48	42	48.11	78.39	83.76	0.9
26	9/3/2011	1/0/00 12:00 AM	51.26	42	48.11	78.39	83.76	0.9
27	9/3/2011	1/0/00 1:00 AM	50.98	42	48.11	77.9	82.61	0.79
28	9/3/2011	1/0/00 2:00 AM	50.21	42	48.11	77.9	82.54	0.73
29	9/3/2011	1/0/00 3:00 AM	49.65	42	48.11	77.9	82.54	0.68
30	9/3/2011	1/0/00 4:00 AM	49.61	42	48.07	77.9	82.54	0.65
31	9/3/2011	1/0/00 5:00 AM	48.48	42	47.38	77.34	82.54	0.48
32	9/3/2011	1/0/00 6:00 AM	48.83	42	47.4	77.17	82.54	0.46
33	9/3/2011	1/0/00 7:00 AM	48.83	42	48.11	76.78	81.54	0.22
34	9/3/2011	1/0/00 8:00 AM	48.02	42	48.11	75.95	81.32	0.06
35	9/3/2011	1/0/00 9:00 AM	48.05	42	48.11	75.95	81.32	-0.06
36	9/3/2011	1/0/00 10:00 AM	49.8	42	48.11	75.95	81.32	0.2
37	9/3/2011	1/0/00 11:00 AM	50.91	42	48.11	75.95	82.05	0.59
38	9/3/2011	1/0/00 12:00 PM	51.83	42	48.11	75.95	82.54	0.59
39	9/3/2011	1/0/00 1:00 PM	53.34	42	48.18	76.74	82.54	0.66
40	9/3/2011	1/0/00 2:00 PM	53.94	42	48.84	77.17	82.54	0.8
41	9/3/2011	1/0/00 3:00 PM	54.11	42	48.84	77.17	82.54	0.87
42	9/3/2011	1/0/00 4:00 PM	54.91	42	48.84	77.17	83.21	0.94
43	9/3/2011	1/0/00 5:00 PM	53.91	42	48.84	77.17	83.76	0.9
44	9/3/2011	1/0/00 6:00 PM	54.52	42	48.84	77.17	83.76	0.92
45	9/3/2011	1/0/00 7:00 PM	53.38	42	48.49	77.17	83.56	0.86
46	9/3/2011	1/0/00 8:00 PM	50.54	42	48.11	77.17	82.54	0.47
47	9/3/2011	1/0/00 9:00 PM	49.9	42	48.11	77.17	81.87	0.26
48	9/3/2011	1/0/00 10:00 PM	48.4	42	48.11	77.17	81.32	0.2
49	9/3/2011	1/0/00 11:00 PM	47.76	42	47.5	77.17	81.32	0.13
Sheet1

now try this macro "test"
and see whether it give you what you want.

Sub test()
Dim c As Range
Dim r As Range, ttime
Dim cfind As Range

Application.ScreenUpdating = False
undo
Worksheets("sheet1").Activate
For Each c In Range(Range("B2"), Range("B2").End(xlDown))
c = c.Offset(0, -1) + c
Next c

Set r = Range(Range("B2"), Range("B2").End(xlDown))
ttime = Range("B5").Value
'MsgBox ttime
Set cfind = r.Find(what:=ttime)
If Not cfind Is Nothing Then
'MsgBox cfind.Address
cfind.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
Do
ttime = ttime + 6 / 24
'MsgBox ttime
Set cfind = r.Find(what:=ttime)
If Not cfind Is Nothing Then
'MsgBox cfind.Address
cfind.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Else
GoTo exitsub
End If
Loop
exitsub:
Range("a1").EntireRow.Copy
Worksheets("sheet2").Activate
Range("a1").PasteSpecial
Range("B1").EntireColumn.AutoFit
Range("a1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Columns("B:B").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

End Sub

 

[venkat1926]

I apologise to the moderators as at the time of posting my solution there was some problem with my computer and Internet connection and my reply was duplicated. SORRY

powerjak

you copy your data in sheet3 from A1 and run the macro "test"
the data I have posted may not be correct.

 

[powerjak]

venkat,

Thanks so much for your reply and code. I'll give this a test run tonight after I end my work day, and get back to you with results and any issues.

Thanks again ... back to you soon!

Sincerely,
powerjak