To create a report that subtotals a variable number of detail lines, this method can be used to process the data.  This Excel macro written in Visual Basic for Applications calculates if an employee absence has been made up:

 

Sub Calculating_If_Absence_Has_Been_Made_Up()

'

' Calculating_If_Absence_Has_Been_Made_Up Macro

‘ Macro VBA code written by Lynn Preston.

'

    'WHEN TO USE MACRO:  Calculating If Employee Absence Has Been Made Up,

    'Procedure:

    '1) Prepare Company Input Data for an Edit to See if Absence Has Been Made Up:

    '   a) Sort all Company Payroll data by SS# with ABS Detail Hourly Record first for

    '   each employee, followed by 0 or > Detail Negative Hourly Records of PTO, Sick,...

    '   b) Insert a blank column to the immediate right of SS# column and LEAVE IT ALL BLANK.

    '   This column is for summing all absent hours by employee.

    '2) Select the data in three columns (SS#, Zero'd out Total, and Absent Hrs columns) and run macro:

    '   MACRO PROCESSING EXPLANATION:

    '   For each detail record, macro sums absent hours until SSN break,

    '   when calculated total is loaded into ee's last detail record.  Sometimes it's "0"!

    '3) Save spreadsheet after macro's done.

    '4) Set data filter on the newly inserted total column; select NON-BLANK filtered items

    '   and copy to the bottom of the spreadsheet under a colored line you insert at the bottom

    '   of your data.

    '5) Set data filter on the type of absence hours column; select "ABS" filtered items

    '   and copy to the bottom of the spreadsheet under a colored line you insert at the bottom

    '   of your data.

    '6) These two groups of copied data should match one for one by employee; copy computed

    '   totals to the end of the ABS records.  This is your final output.  Duplicate datasheet

    '   within spreadsheet and create a report in a "PRINT" tab.

 

    ' Dimension variables

 

    Dim xRng, xCounter, xSumCounter, xHours_Sum_Total_By_Employee As Integer

    Dim ID_Current, ID_Prior, First_ID_of_Match_to_Compare As String

    First_ID_of_Match_to_Compare = "Y"

    xHours_Sum_Total_By_Employee = 0

    xSumCounter = 0

           

    Set xRng = Selection ' uses user-selected range in Excel.

    'Loop once for every row in the selected three columns.

         

    For xCounter = 1 To xRng.Cells.Count

        If xCounter = 1 Then

        ' On first cell read in range, initialize ID Compare Values

            ID_Current = xRng.Cells(xCounter).Value

            ID_Prior = xRng.Cells(xCounter).Value

            First_ID_of_Match_to_Compare = "Y"

            ' Initialize Employee Hours Total Sum Variable with Current Employee Hours

            xSumCounter = 0

            xSumCounter = xCounter + 2

            xHours_Sum_Total_By_Employee = xRng.Cells(xSumCounter).Value

        End If

               

        If xCounter > 1 Then

       

            're-set current ID compare field

            ID_Current = xRng.Cells(xCounter).Value

           

            If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "N" Then

                'AFTER AN ID MISMATCH:

                '   Process old employee: Fill total field which is in prior detail record

                xSumCounter = 0

                xSumCounter = xCounter - 2

                xRng.Cells(xSumCounter).Value = xHours_Sum_Total_By_Employee

                '   Reset prior compare ID and first ID of match flag

                ID_Prior = ID_Current

                First_ID_of_Match_to_Compare = "Y"

                ' Initialize Employee Hours Total with Current Employee Hours

                xSumCounter = 0

                xSumCounter = xCounter + 2

                xHours_Sum_Total_By_Employee = xRng.Cells(xSumCounter).Value

             Else

                If ID_Current = ID_Prior Then

                    'MULTIPLE EMPLOYEE DETAIL RECORDS

                    'Accumulate sum total of employee hours:

                    First_ID_of_Match_to_Compare = "N"

                    xSumCounter = 0

                    xSumCounter = xCounter + 2

                    xHours_Sum_Total_By_Employee = xHours_Sum_Total_By_Employee + xRng.Cells(xSumCounter).Value

                Else

                    If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "Y" Then

                        ID_Prior = ID_Current

                        'Process old employee: Fill total field which is in prior detail record

                        xSumCounter = 0

                        xSumCounter = xCounter - 2

                        xRng.Cells(xSumCounter).Value = xHours_Sum_Total_By_Employee

                        'Initialize Employee Hours Total with Current Employee Hours

                        xSumCounter = 0

                        xSumCounter = xCounter + 2

                        xHours_Sum_Total_By_Employee = xRng.Cells(xSumCounter).Value

                        First_ID_of_Match_to_Compare = "Y"

                    End If

                End If

            End If

        End If

       

     'skip column over

     xCounter = xCounter + 2

    

    'go to next row to the cell with an ID # in it

     Next xCounter

    

'AT END OF FILE:

'   Process last employee: Fill total field which is in prior detail record

 xSumCounter = 0

 xSumCounter = xCounter - 2

 xRng.Cells(xSumCounter).Value = xHours_Sum_Total_By_Employee

End Sub

 

 

 

To reconcile a client’s payment against your company’s invoice, this worksheet may be used (spreadsheet created by Lynn Preston):

 

On 3/04/2008, Posting of 3/08 Bill:

 

 

 

 

Credit Amount Forward:

 $                        (670.68)

 

 

Group Contribution Amount for 315 Employees:

 $                   137,445.80

 includes $25 admin fee

 

Total Amount Due This Month:

 $                   136,775.12

 

 

Co. Paid for the 3/08 Bill:

 $                 (135,013.26)

 

 

Credit Amount Forward Toward 4/08 Bill:

 $                       1,761.86

 

 

 

 

 

 

 

Co. Intended to Pay for 3/08 Bill, printed on 2/18/2008:

 

 

Credit Amount Forward:

 $                     (1,210.73)

 

 

Group Contribution Amount for 311 Employees:

 $                   135,259.48

 includes $25 admin fee

 

Total Amount Due This Month:

 $                   134,048.75

 

 

Employee Deleted for 3/08:

 

 

 

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (212.23)

 

 

 

 

 $                        (212.23)

 co. wrote on 3/08 bill

 

Employees Added for 3/08:

 

 

 

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          525.68

 

 

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          651.06

 

 

 

 

 $                       1,176.74

 co. wrote on 3/08 bill

 

Co. Paid for the 3/08 Bill:

 $                   135,013.26

 co. wrote on 3/08 bill

 

 

 

 

 

 

Detailed Explanation of Difference:

 $                       1,761.86

 

 

NOTE TO PREPARER:  Subtract from printed bill; Add from actual posting.

 

 

Employees Deleted for 3/08:

 

 

1

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (651.06)

 

2

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (212.23)

 

3

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (212.23)

 

4

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (212.23)

 

 

 

 

 $                     (1,287.75)

 CO. OVERPAID

 

Employees Added for 3/08:

 

 

1

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          212.23

 

2

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          651.06

 

3

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          212.23

 

4

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          212.23

 

5

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          212.23

 

6

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          212.23

 

7

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          358.52

 

 

 

 

 $                       2,070.73

 CO. UNDERPAID

 

Employees Changing Rates for 3/08:

 

 

1

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                        (212.23)

 

1

#A-9999-9999

ANONYMOUS EMPLOYEE

 $                          651.06

 

 

 

 

 $                          438.83

 CO. UNDERPAID

 

Credit Amount Forward on 3/08 bill, printed on 02/18/08:

 $                       1,210.73

 Co. took credit amount.

 

Actual Credit Amount Forward When Posting 3/08 Bill:

 $                        (670.68)

 

 

 

 

 $                       1,761.86

 

 

 

 

 

 

 

NOTE TO PREPARER:  Consider making a summary report if many charges are cancelling each other out.