Building a template report

[Demonsguile]

Currently, I'm spending many hours reading a report and hand-typing values from the report into an Excel spreadsheet. I do this for each day of the year. It's quite time-consuming and I'm looking to find a faster approach. The report is in PDF format, but I can export it to Excel.

Here is an example of the data:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]00:00 - 00:29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sub Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]00:30 - 00:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sub Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

Please note that the spacing here is accurate. The are occasions where there is one empty cell between the time and the word "Sub Total" and other occasions where there are two spaces. But there will never be more than two spaces.

My goal is to get value X for each of the defined timeframes and list each one in a column format. The end-game would be for me to copy/paste in the report data and have the system auto-calculate the report below:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0:00[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0:30[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

Any assistance would be GREATLY appreciated.

Thanks,
DG

 

[L. Howard]

Give this a try, copied to a standard module.

Using your example report copied to A1, (where 00:00 - 00:29 is in cell A1), and the results returned in columns K, L, M.


Howard

Sub Demonsguile_Code()
Dim oneRng As Range
Dim c As Range
Dim oRow As Long

Set oneRng = Sheets("Sheet2").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

oRow = 0

For Each c In oneRng

   If Left(c, 3) = "Sub" Then
   
      oRow = oRow + 1
      Sheets("Sheet2").Range("K" & Rows.Count).End(xlUp)(2) = oRow
      Sheets("Sheet2").Range("L" & Rows.Count).End(xlUp)(2) = c.Offset(0, 0).End(xlUp)
      Sheets("Sheet2").Range("M" & Rows.Count).End(xlUp)(2) = c.Offset(1, 4)
     
    End If
 
Next
End Sub

 

[Demonsguile]

Howard,
Thank you for providing this! I did come up with a very crude solution that should work.
I created a column with each possible timeframe (00:00 - 00:29, etc). Then, I used =CELL("address"....) to identify the specific cell that each timeframe is in. Using an =OFFSET(INDIRECT(*cell with the previous formula*), 3, 4) provided me with the value in the first scenario. But, because there are two possible offsets for each timeframe, in an adjacent cell I recreated the offset formula, but this time I dropped one more row. This provided me with two values for each timeframe. However, one of them should always be 0. So, in the next adjacent cell I used a simple =MAX(...) formula against these two values which gives me the answer the correct value.
That appears to work. At least with my one set of test data.

However, your solution looks much more elegant. I'll try it out today, when I get to work.

 

[L. Howard]

Okay, sounds good.

The "time" value returned with the code is the entire cell content, i.e. 00:00 - 00:29, 00:30 - 00:59.
With a small change in the code it can be 0:00, 0:30, more like you show in your desired results if need be.

Howard