Collect data and sum, w/ connection to another sheet

[Ed]

I have a macro program which searches through Word documents for data points
and writes them into an Excel worksheet. At the moment, it is one row per
document, with 17 columns of data per row.

One of the data points to this time has simply been a "Yes" or "No",
depending on whether certain times and charges were present or not. If they
are present, though, there are usually (but not always) multiple lines of
times and charges. I have figured out how to grab each time and charge from
each applicable line and write them to a separate worksheet along with the
invoice number (invoice number in Col A, ThisTime in Col B, ThisCharge in
Col C, TotalCharge in Col D, etc.) for each report document.

What I haven't figured out is how to sum each of these individually by
invoice number, and have those sums appear on the main summary worksheet.
For instance, the TimesAndCharges worksheet might have:

A123 2.0 45.00 90.00
A125 1.0 30.00 30.00
A125 2.5 45.00 112.50
A125 1.0 45.00 45.00

The Summary sheet would then have:

A122 ~these columns blank~
A123 2.0 45.00 90.00
A124 ~these columns blank~
A125 1.0 30.00 30.00 3.5 45.00 157.50

So I would need to:
-- identify the start and stop of each separate range on the TimesAndCharges
worksheet on the fly as the Word docs are scanned,
-- sum each range according to value, and
-- identify the corresponding row on the Summary sheet and write my sums.

Where do I start for a good approach?

Ed

[[email protected]]

Hi
You could maintain collections.
You have 17 columns of data per row. I'll assume the invoice number is
in the first column. I'll assume your data starts in row 2 (row1 is a
header row?) and has a range name called "InvoiceData" which includes
the header row.

The function below outputs a collection of "rows" with the sums in.

Function MakeSums() As Collection
Dim DataVariant As Variant
Dim Datarows As Long
Dim TempSums(1 To 1, 1 To 17) As Variant
Dim TempVariant As Variant
Dim i As Long, j As Long
Dim TestCollection As New Collection, SumCollection As New Collection
DataVariant = ActiveSheet.Range("InvoiceData").Value
Datarows = UBound(DataVariant, 1)
On Error Resume Next
For i = 2 To Datarows
Err.Clear
TestCollection.Add DataVariant(i, 1), Trim(CStr(DataVariant(i,
1)))
If Err.Number = 0 Then
For j = 1 To 17
TempSums(1, j) = DataVariant(i, j)
Next j
TempVariant = TempSums
Else
TempVariant = SumCollection(Trim(CStr(DataVariant(i, 1))))
SumCollection.Remove Trim(CStr(DataVariant(i, 1)))
For j = 2 To 17
TempVariant(1, j) = TempVariant(1, j) + DataVariant(i,
j)
Next j
End If
SumCollection.Add TempVariant, Trim(CStr(DataVariant(i, 1)))
Next i
Set MakeSums = SumCollection
End Function

The sub below writes these rows to the "Summary Sheet", again starting
at row 2.
Sub OutputSums()
Dim OutputCollection As Collection
Dim Item As Variant
Set OutputCollection = MakeSums
With Worksheets("Summary Sheet")
i = 2
For Each Item In OutputCollection
.Cells(i, 1).Resize(1, 17).Value = Item
i = i + 1
Next Item
End With
End Sub

regardsPaul

[Ed]

Thanks, Paul! I'm going to have to look at this for a bit to make sure I
understand what's happening and how to match it to what I've already got. I
really appreciate the time and effort.
Ed

<[email protected]> wrote in message
news:[email protected]...
> Hi
> You could maintain collections.
> You have 17 columns of data per row. I'll assume the invoice number is
> in the first column. I'll assume your data starts in row 2 (row1 is a
> header row?) and has a range name called "InvoiceData" which includes
> the header row.
>
> The function below outputs a collection of "rows" with the sums in.
>
> Function MakeSums() As Collection
> Dim DataVariant As Variant
> Dim Datarows As Long
> Dim TempSums(1 To 1, 1 To 17) As Variant
> Dim TempVariant As Variant
> Dim i As Long, j As Long
> Dim TestCollection As New Collection, SumCollection As New Collection
> DataVariant = ActiveSheet.Range("InvoiceData").Value
> Datarows = UBound(DataVariant, 1)
> On Error Resume Next
> For i = 2 To Datarows
> Err.Clear
> TestCollection.Add DataVariant(i, 1), Trim(CStr(DataVariant(i,
> 1)))
> If Err.Number = 0 Then
> For j = 1 To 17
> TempSums(1, j) = DataVariant(i, j)
> Next j
> TempVariant = TempSums
> Else
> TempVariant = SumCollection(Trim(CStr(DataVariant(i, 1))))
> SumCollection.Remove Trim(CStr(DataVariant(i, 1)))
> For j = 2 To 17
> TempVariant(1, j) = TempVariant(1, j) + DataVariant(i,
> j)
> Next j
> End If
> SumCollection.Add TempVariant, Trim(CStr(DataVariant(i, 1)))
> Next i
> Set MakeSums = SumCollection
> End Function
>
> The sub below writes these rows to the "Summary Sheet", again starting
> at row 2.
> Sub OutputSums()
> Dim OutputCollection As Collection
> Dim Item As Variant
> Set OutputCollection = MakeSums
> With Worksheets("Summary Sheet")
> i = 2
> For Each Item In OutputCollection
> .Cells(i, 1).Resize(1, 17).Value = Item
> i = i + 1
> Next Item
> End With
> End Sub
>
> regardsPaul
>

[Ed]

Paul - I made a mistake. The time data is not formatted as numbers but as
hours:minutes.
Not:
A123 2.0 45.00 90.00
A125 1.0 30.00 30.00
A125 2.5 45.00 112.50
A125 1.0 45.00 45.00

but as:
A123 02:10 45.00 90.00
A125 01:20 30.00 30.00
A125 02:35 45.00 112.50
A125 01:45 45.00 45.00

Try as I might, when inside VBA and trying to collect these values, they
want to be clock time, not an hour and minute value. How do I format
"02:35" to be "2 hours and 35 minutes", instead of "2:35 AM"?

Ed

<[email protected]> wrote in message
news:[email protected]...
> Hi
> You could maintain collections.
> You have 17 columns of data per row. I'll assume the invoice number is
> in the first column. I'll assume your data starts in row 2 (row1 is a
> header row?) and has a range name called "InvoiceData" which includes
> the header row.
>
> The function below outputs a collection of "rows" with the sums in.
>
> Function MakeSums() As Collection
> Dim DataVariant As Variant
> Dim Datarows As Long
> Dim TempSums(1 To 1, 1 To 17) As Variant
> Dim TempVariant As Variant
> Dim i As Long, j As Long
> Dim TestCollection As New Collection, SumCollection As New Collection
> DataVariant = ActiveSheet.Range("InvoiceData").Value
> Datarows = UBound(DataVariant, 1)
> On Error Resume Next
> For i = 2 To Datarows
> Err.Clear
> TestCollection.Add DataVariant(i, 1), Trim(CStr(DataVariant(i,
> 1)))
> If Err.Number = 0 Then
> For j = 1 To 17
> TempSums(1, j) = DataVariant(i, j)
> Next j
> TempVariant = TempSums
> Else
> TempVariant = SumCollection(Trim(CStr(DataVariant(i, 1))))
> SumCollection.Remove Trim(CStr(DataVariant(i, 1)))
> For j = 2 To 17
> TempVariant(1, j) = TempVariant(1, j) + DataVariant(i,
> j)
> Next j
> End If
> SumCollection.Add TempVariant, Trim(CStr(DataVariant(i, 1)))
> Next i
> Set MakeSums = SumCollection
> End Function
>
> The sub below writes these rows to the "Summary Sheet", again starting
> at row 2.
> Sub OutputSums()
> Dim OutputCollection As Collection
> Dim Item As Variant
> Set OutputCollection = MakeSums
> With Worksheets("Summary Sheet")
> i = 2
> For Each Item In OutputCollection
> .Cells(i, 1).Resize(1, 17).Value = Item
> i = i + 1
> Next Item
> End With
> End Sub
>
> regardsPaul
>

[[email protected]]

Hi
It's tricky. 02:30 is itself not well defined as a time as it needs an
AM or PM on the end. Then you might be able to use the HOUR and MINUTE
functions to extract the hours and minutes.
Where are values like 02:30 coming from? Can you alter them at source
with a search replace and change them to 02.30 before you import into
Excel?

regards
Paul

[Ed]

> Where are values like 02:30 coming from? Can you alter them at source
> with a search replace and change them to 02.30

Thanks for responding, Paul. No, this is a plain text report doc output
from a database. As far as I know, I'm the only person interested in doing
anything like this, so they're not going to alter the report generator for
me. 8>{

I'm thinking it might be easier to separate each time string into hours and
minutes values as I grab it, and do calculations that way. The only problem
then becomes how to handle going over 60 minutes. For some reason, when
programming for TIME, the programmers thought in clock times and not simply
the quantity of hours and minutes.

Ed

<[email protected]> wrote in message
news:[email protected]...
> Hi
> It's tricky. 02:30 is itself not well defined as a time as it needs an
> AM or PM on the end. Then you might be able to use the HOUR and MINUTE
> functions to extract the hours and minutes.
> Where are values like 02:30 coming from? Can you alter them at source
> with a search replace and change them to 02.30 before you import into
> Excel?
>
> regards
> Paul
>

[[email protected]]

Hi
Grab the hours and multiply by 60, then add to the minutes?

regards
Paul

[[email protected]]

Hi
Grab the hours and multiply by 60, then add to the minutes?

regards
Paul

[Ed]

That was my conclusion as well. Excel does have the TIME function, but it
still requires splitting the number. Still, that might be easier to use
because it automatically deals with minutes over 59.

Thanks for your interest and help, Paul. I appreciate the boost.
Ed

<[email protected]> wrote in message
news:[email protected]...
> Hi
> Grab the hours and multiply by 60, then add to the minutes?
>
> regards
> Paul
>