Payroll Data Organizer
Excel macros to automate the organization of payroll error records.
Scenario:
These macros are applied to a situation where employee payroll data contains records with errors. The error records have been extracted from the raw data. The macros identify the employees associated with the error records and copy the row with the employee name into the error sheet. It then counts and summarized the number of records.
VBA Source Code:
– Macros are written in VBA for Microsoft Excel
– Template file is preloaded with the macros
– Code can be modified to suit your goals
Organized error records:
– Spaces are inserted between distinct SSNs
– Employee identifying lines are inserted
– Records for each employee are counted
Java Version:
– Built on JDK 17
– Platform independent
– Accomplishes the task like the VBA and Bash scripts
Raw Employee Data Records:
– Rows starting with “2” are employee identifying rows
– Identifying rows contain employee SSN and full name
– Rows starting with “5” are payroll data records
Bash Script Version:
– Runs on Linux
– Reads in data from .txt documents
– Outputs organized data, also in .txt
Dig into the details at the Github page.
Full Summary:
The use case for these macros is a situation when there is one file containing employee pay data (Sheet1). There are two types of records in this sheet. Records which start with the number “2” are employee identifying lines, which show the employee’s first name and last name, with their SSN preceeding it (fake SSN, in this case), and two spaces after their name. The other type of record starts with a “5” and contains detail data. This example contains meaningless strings of data, but in a real-world scenario these records could contain the pay period numbers or dates, hours worked, overtime worked, leave taken, etc.
These detail records sometimes contain errors. For example, there may be duplicate pay period records that generated somehow, the employee numbers might be missing or incorrect, etc. These macros are utilized when rows with errors have been identified and extracted from the Sheet1 data and placed in Sheet2. These are all detail records which start with “5”, and not employee identifying lines which start with “2”.
The goal is to match the extracted error lines in Sheet2 with their corresponding Employee. Doing this manually is very time consuming, but the Step 1 macro automates this task. It identifies when the social security number (just capital letters in this case) changes from one row to the next. Then, it places two blank rows between the records, and fetches the appropriate row starting with “2” from Sheet1, and copies it at the beginning of each block repeated social security numbers by matching the SSNs between the two sheets.
Next, the Step 2 macro will place a count of the number of error records that exist for each employee in Sheet 2. Finally, Step 3 provides summary data, and places just the employee names and number of records in the worksheet.