MIP Toolbox

ULEC Benefit Allocation — User Guide

Back to the tool

ULEC Benefit Allocation

The ULEC Benefit Allocation tool takes a GIS Benefits export and enriches it with an Employee ID (from an employee mapping file) and a Product Code (from a column mapping file), then splits the rows into two output files — one for rows with an employer portion, and one for rows where the employer portion is zero. Session metadata is appended to every row.

Primary input

GIS Benefits (XLSX, XLS, CSV)

Mapping files

Employee + Column (optional)

Output

Two .xlsx files (split by employer portion)

Sheet read

"DataFile" tab (falls back to 2nd sheet)

Step 1.Upload GIS Benefits File

Upload your primary GIS Benefits export. The tool reads the "DataFile" tab; if that tab isn't present, it falls back to the second sheet, then the first. XLSX, XLS, and CSV are all accepted.

The file should contain a header row with recognizable keywords (Product, Payroll, Premium, Coverage, Volume) — the tool scans the first five rows to find it, skipping any title rows.

This file is required — the Process button only appears once it's uploaded.

Step 2.Upload Mapping Files

Employee Mapping File (optional)

Maps employees to their allocation details. The tool uses it to fill the Employee ID column by looking up the SSN in column H (index 7) and returning the Employee ID from column B (index 1). If a GIS row's first column already contains an alphanumeric ID (e.g. A002), that ID is used directly instead.

When SSNs don't match exactly (masked vs. unmasked), the tool falls back to matching on the last four digits.

Column Mapping File (optional)

Defines how product names map to product codes. The tool reads the product name from column A (index 0) and the code from column B (index 1). The A1 heading of this file is used to find the matching product column in the GIS file; if no exact header match is found, the tool falls back to keyword detection (Product, Benefit, Plan, Coverage) and then to matching known product names against the data.

Product matching is case- and punctuation-insensitive, with prefix matching for robustness (e.g. "Basic Life" matches "Basic Life and AD&D").

Step 3.Session Settings

Enter optional metadata that is appended to every output row:

  • Session ID — e.g. ULEC-BA-2026-001. Also used in the output file name.
  • Session Description — a brief description of this session.
  • Date — formatted and written to each row's Date column.

Step 4.Process, Review & Download

Click Process File. The tool reads the GIS data, finds the SSN and product columns, looks up Employee IDs and Product Codes, drops summary rows ("Report Total", "Report Difference"), and splits the data by the Employer Portion column.

The results panel shows how many rows were processed, how many Employee IDs and Product Codes matched, which columns were detected, and a preview of the first 25 rows. If any matches failed, the detection summary helps you confirm the right columns were used.

Download the output:

  • Download File — the "Processed" sheet: rows where Employer Portion is not zero.
  • Download Zero Employer File — appears only when there are rows with a zero employer portion; these are written to a separate "Zero Employer Portion" sheet.

Both files keep the original GIS columns, add Employee ID and Product Code as columns M and N, and append Session ID, Session Description, and Date columns. An autofilter is applied to each sheet.

Tips

  • Give your GIS file a "DataFile" tab to be sure the right sheet is read.
  • If Employee ID or Product Code columns come back blank, check the detection summary — the SSN or product column may not have been found, or the mapping file headers may not match.
  • The Employee Mapping lookup works on the full SSN first, then the last four digits, so masked SSNs can still match.
  • Rows with a zero Employer Portion are always split into a second file so they can be reviewed separately.
  • Use Reset to clear all files, session fields, and results and start over.
Open ULEC Benefit Allocation