Turns any carrier census spreadsheet into a ready-to-upload LaunchMyGroup file — plus an NCAM quoting sheet — right inside Excel. Your census data never leaves your workbook.
You only do this once. The script is saved to your Office account, so it shows up in the Automate tab of every workbook you open from now on.
Safe to re-run as many times as you like: the first run saves your original data to a RAW DATA tab and never touches it again — later runs reprocess from that untouched copy.
The script doesn't update itself — when a new version ships, you paste the new copy over the old one:
Want to know when a new version is out? Sign up for update emails below — no need to keep checking this page.
Three deliberately messy test censuses — run the script on one of these before pointing it at a real file, so you know what to expect.
Flat sample — triggers most cleanup rules (split address, spelled-out states, hourly pay, a retiree, a bad row, a flagged duplicate).
⬇ SAMPLE-census.xlsx
Stacked sample — one person spread across several product rows, with dependents stored in columns; the tool collapses each person to one clean row.
⬇ SAMPLE-stacked-census.xlsx
Product-coded sample — the hard case: dental and vision share one generic "Coverage Tier" column, told apart by a "Plan Type" column; the tool routes each tier to the right product.
⬇ SAMPLE-product-coded.xlsx
Something mapped wrong, flagged wrong, or errored? Describe what happened — the census layout, what you expected, what you got. Don't paste real employee data (names/SSNs) — column headers are fine.
Leave your name and email and pick what you want to hear about. No spam — just news about this tool.
As you confirm columns on the HEADER MAP tab, copy that tab's SHARE column (each line is Source Header | LMG Field) and paste it below. Good mappings get folded into the built-in dictionary so future versions recognize them automatically — for everyone.
# LMG / NCAM Census Translation Engine — User Guide (v1.0)
A one-click Excel tool that converts a carrier census into the LaunchMyGroup
(LMG) upload format and a derived NCAM quoting sheet, with full validation and
an audit trail. It runs **inside Excel** — no install, no Python, no website.
---
## 1. What it does
Point it at a messy census file and press **Run**. In a few seconds your
workbook is rebuilt into six tabs:
| Tab | What it is |
|-----|------------|
| **LMG** | Your clean enrollment file, ready to upload. Always tab #1. |
| **NCAM** | Employees-only sheet in quoting layout. |
| **SUMMARY** | Counts by relationship and by plan tier (EE/ES/EC/FAM/W). |
| **VALIDATION ISSUES** | Every problem found, rated ERROR / WARNING / INFO. |
| **PROCESSING LOG** | A record of every transformation it performed. |
| **RAW DATA** | An untouched copy of your original census. Never modified. |
| **Other Columns** | Every column kept out of the LMG upload, with the reason — blank LMG fields, unrecognized source headers, and duplicate columns. The data still lives on RAW DATA. |
The **LMG** and **NCAM** tabs are colored green; the rest are navy. On the
**LMG** tab, any cell with a validation issue is highlighted by severity
(red = error, amber = warning, blue = info), so problems jump out at a glance.
It standardizes headers, merges split address fields, converts state names to
2-letter codes, cleans phones to 10 digits, formats dates to MM/DD/YYYY,
normalizes gender to M/F, converts coverage to EE/ES/EC/FAM/W, abbreviates long
job titles, annualizes hourly wages, and more.
**It also reshapes the two "nightmare" layouts before cleaning:** if a person is
split across several rows (one per line of coverage — Dental, Vision, Life…), it
**collapses them into one row**; if dependents are stored sideways as columns
(`Dependent 1 Name`, `Dependent 2 …`), it **explodes them into their own
Spouse/Child rows**. It only reshapes when it's confident, and flags anything it's
unsure about. Try `SAMPLE-stacked-census.xlsx` to see it.
---
## 1b. Rolling this out to your team
**The Automate tab (Office Scripts) is enabled on the Humana network** —
confirmed — so this is the right primary tool for the team. Everyone uses their
Humana M365 account in Excel Online or desktop Excel.
**Share one copy so everyone runs the same logic.** Two mechanisms, with an
important difference:
- **Share into a workbook** (script's **⋯** menu → **Share**): attaches the
script to *that workbook only* — anyone opening that workbook can run it, but
it doesn't follow them to other files. Fine for a shared template; not a
team-wide rollout.
- **Save the script to a shared SharePoint/Teams library** (script's **⋯**
menu → **Save As** → pick a SharePoint site your team can access): everyone
adds that location once in **Automate → All Scripts** and runs the *same
file* in any workbook. When the engine updates, you paste the new version
into that one script and **the whole team is updated — no re-pasting for
anyone else.** This is the recommended way to manage versions for a team.
If neither is available on your tenant, everyone maintains their own copy and
updates it from the hub page (see section 2c — it takes ~20 seconds).
**Universal fallback (off-network / non-Humana users):** the offline HTML
cleaner (`CensaScrub/lmg_census_cleaner_offline.html`) runs in any browser with
no account and keeps data on the machine. It implements the same
`CENSUS-RULES.md`, so output matches.
## 2. One-time setup (≈2 minutes)
You need **Excel for the web** or **Excel on desktop with a Microsoft 365
work account** (the **Automate** tab must be visible on the ribbon).
1. Open the census workbook in Excel.
2. Click the **Automate** tab on the ribbon → **New Script**.
3. Delete the sample code in the editor.
4. Open `LMG-NCAM-Census-Engine.ts`, copy **all** of it, and paste it in.
5. Rename the script to `LMG NCAM Census Engine` and click **Save script**.
That script now lives in your account. For every future census you only do
section 3.
---
## 2b. Confirming you're on the latest version (for testing)
The script carries a version number. When it runs, that version is printed on the
**SUMMARY** tab (`Engine version`) and as the first line of the **PROCESSING LOG**
tab. The hub page (lmg-ncam-engine.netlify.app) shows the latest version at the
top. **If the version in your output doesn't match the hub, re-copy the script.**
This is how we stay lined up while testing.
## 2c. Updating to a new version (≈20 seconds)
The script doesn't update itself. When the hub shows a newer version:
1. On the hub page, click **Copy script**.
2. In Excel: **Automate** tab → click **LMG NCAM Census Engine** → **Edit**.
3. Click inside the code, **Ctrl+A** (select all), **Ctrl+V** (paste),
then **Save script**.
You can also sign up on the hub page to get an email when a new version ships.
## 3. Running it on a census (every time)
1. Open the census workbook.
2. Make sure the tab with the raw census data is the **active** tab
(just click it). The engine reads from the active tab, or from a tab named
`RAW DATA` if one already exists.
3. **Automate** tab → find `LMG NCAM Census Engine` → **Run**.
4. When it finishes, you'll land on the **LMG** tab.
**Always review the VALIDATION ISSUES tab before you upload.** See section 5.
It is safe to run more than once. The first run snapshots your original to
`RAW DATA`; every later run reprocesses from that untouched snapshot, so you
can fix the source and re-run without losing anything.
---
## 4. What gets cleaned (the rules)
- **Headers** — parenthetical text removed (`RELATIONSHIP (EE, SP, CH)` →
`RELATIONSHIP`); common variants mapped to LMG field names (`SSN`,
`Soc Sec No`, `Tax ID` → the SSN column).
- **Relationship** → `Employee`, `Spouse`, `Domestic Partner`, `Child`.
Unknown values (e.g. `Retiree`) are flagged, not guessed.
- **Names** — Last/First UPPERCASE, Middle Initial trimmed to one letter,
Relationship in Proper Case.
- **Gender** — `Male`→`M`, `Female`→`F`. Anything else is flagged.
- **Dates** (DOB, hire, prior-carrier) → `MM/DD/YYYY`. Handles slashes,
dashes, ISO, 2-digit years, and Excel date serials. Unparseable dates flagged.
- **SSN** — digits only, must be 9. Employees missing an SSN are flagged.
- **Address** — `Address 1` + `Address 2` + `Address 3` merged into one
`Member Street Address`, UPPERCASE.
- **State** — full names → USPS (`Texas`→`TX`). Invalid states flagged.
- **Zip** — first 5 digits; restores a dropped leading zero.
- **Phone** — digits only, 10 digits; strips a leading country `1`. Bad
phones are flagged and left blank rather than uploaded wrong.
- **Salary** — `$50,000` → `50000`. `$25.50/hr` with 40 hours →
`25.50 × 40 × 52 = 53040`. A salary under $1,000 is flagged as a possible
hourly rate.
- **Hours** — blank employee defaults to `40`.
- **Occupation** — UPPERCASE, common abbreviations (`SENIOR`→`SR`,
`ADMINISTRATOR`→`ADMIN`, …), max 25 characters; over-long titles flagged.
- **Disabled?** — blank defaults to `No`.
- **Coverage tier** — normalized to `EE / ES / EC / FAM / W`.
- On **LMG**, a waived dental/vision tier becomes
`Waive with other coverage`; a blank employee tier (when the group
carries that product) is also forced to `Waive with other coverage`.
- On **NCAM**, those convert back to `W` (because NCAM defaults blanks to EE).
- **Primary Beneficiary** — if an employee has Basic or Voluntary Life but no
beneficiary, it's set to `ON FILE`.
- **Voluntary Life** — employee blank/waive amounts become `0`.
---
## 5. Reading the VALIDATION ISSUES tab
| Severity | Meaning | Do before upload? |
|----------|---------|-------------------|
| **ERROR** | Missing/invalid required data (no SSN, bad DOB, no gender). | **Yes — fix.** |
| **WARNING** | Likely wrong but not blocking (odd salary, unknown relationship, **possible duplicate**). | Review. |
| **INFO** | FYI only (e.g. spousal vol-life on a dependent row). | Optional. |
Each issue lists the row number, member name, column, the offending value, the
problem, and a recommended action. Fix the issues on your source tab and re-run,
or fix them directly on the LMG tab before uploading.
## 5b. Mapping columns the tool didn't recognize (HEADER MAP tab)
When the tool isn't sure where a column belongs, it doesn't guess silently — it
puts it on the **HEADER MAP** tab. To include such a column:
1. Open the **HEADER MAP** tab. Each row is a column the tool couldn't match
exactly, with a **SUGGESTED** field, a **CONFIDENCE** %, and a blank
**MAP TO LMG FIELD** cell.
2. In **MAP TO LMG FIELD**, type the LMG field it should become (copy the exact
name from the **LMG** tab header — e.g. `Dental Coverage Tier`).
3. **Re-run** the script. The column is now mapped and included.
The tool reads your **MAP TO** entries first (they override everything), and they
**persist** — the next file from the same carrier maps cleanly without redoing it.
A very close match (≥85%) is applied automatically and marked "verify" so you can
check it; clear its **MAP TO** cell to undo.
### Duplicates are flagged, not deleted
If two rows share the same last name + first name + DOB + gender, the engine
flags them as a WARNING. It does **not** delete a row — you decide which to
remove. This is deliberate: an upload file should never silently drop an
enrollee.
---
## 6. Uploading to LaunchMyGroup
The **LMG** tab is your upload file and is always worksheet #1 (LMG uploads the
first sheet only). Either upload the workbook as-is, or right-click the **LMG**
tab → **Move or Copy** → *(new book)* to export just that sheet.
---
## 7. Known limits in v1.0 (by design)
- **No automatic family-unit merge.** Coverage tiers come from the values in
the census, not from re-deriving who belongs to which family.
- **Duplicates are flagged, not merged.**
- **Voluntary-life roll-ups** (spouse/child amounts moving onto the employee
row) are flagged for manual handling.
- **NCAM** uses the spec's minimum quoting columns plus Salary/Occupation/STD/
LTD when disability coverage is present.
These are the planned next versions. The fastest way to mature them is to run
real census files through v1.0 and feed the edge cases back in.
---
## 8. If something looks wrong
1. Check **PROCESSING LOG** — it lists what the engine did.
2. Check **RAW DATA** — confirm the original value.
3. Note the case, the column, the source value, and the wrong output, and bring
that example back to your AI assistant with the reference blurb
(`AI-REFERENCE-BLURB.md`). Real examples improve the tool far faster than
guessing at carrier formats up front.
# Census Translation Rules — Canonical Spec v1.1
**This document is the single source of truth for every LMG/NCAM census tool.**
The Office Script, the CensaScrub Python tools, and the offline HTML cleaner
should each implement *these* rules so they produce identical output and don't
drift apart. When a rule changes, change it **here first**, bump the version,
then update each implementation.
| Implementation | Repo / file | Runtime | Audience |
|----------------|-------------|---------|----------|
| LMG/NCAM Office Script | `lmg-ncam-engine/LMG-NCAM-Census-Engine.ts` | Excel Automate tab (M365) | Excel + LMG uploaders, zero-install |
| Python CLI/GUI | `CensaScrub/censascrub_v001.py`, `censascrub_gui.py` | Windows desktop | Local power users |
| Offline HTML cleaner | `CensaScrub/lmg_census_cleaner_offline.html` | Any browser, offline | **Universal fallback — any user, no account** |
| ~~Web app~~ (archived 2026-06-29) | `_archive/census-data-refiner/` — dead, never deployed; not a sync target | — | — |
Column source of truth: `CensaScrub/LMG_Column_Dictionary.xlsx` (LMG platform
field names + alias variants).
---
## 1. Workbook / output structure
Seven outputs, in this order. **LMG must be worksheet #1** (LaunchMyGroup uploads
the first sheet only).
1. `LMG` — full enrollment upload (employees, spouses, domestic partners, children). Tab #1, colored `#5C9A1B`.
2. `NCAM` — employees-only quoting sheet derived from LMG. Tab #2, colored `#5C9A1B`.
3. `SUMMARY` — counts by relationship and by plan tier.
4. `VALIDATION ISSUES` — ERROR / WARNING / INFO with row, member, column, value, issue, action.
5. `PROCESSING LOG` — audit trail of every transformation.
6. `RAW DATA` — untouched copy of the original census. Never modified.
7. `Other Columns` — every column **not** in the LMG upload, each with a reason: entirely-blank LMG fields (removed because blank columns break the upload), unrecognized source headers, and duplicate/unused source columns. Two columns: COLUMN, WHY IT IS NOT IN THE LMG UPLOAD. Tab colored Plum `#AF0061`. All other secondary tabs are colored `#003057`.
**Blank-column removal:** after processing, any LMG output column with no data in
any row is dropped from the `LMG` sheet and its name listed on `Other Columns`.
**Exception — REQUIRED columns are never dropped** (Relationship, Member Last
Name, Member First Name, Gender, Date of Birth, State); if a required column is
entirely blank it is kept and flagged ERROR. NCAM and SUMMARY read from the full
(unfiltered) records, so they are unaffected.
**Validation highlighting:** each issue's offending cell is highlighted on the
`LMG` tab by severity — ERROR `#FFC7CE`, WARNING `#FFEB9C`, INFO `#BDD7EE`
(most-severe color wins per cell; duplicate flags highlight the whole row). The
SEVERITY column on `VALIDATION ISSUES` is shaded the same colors.
A tool may read its source from `RAW DATA` if present (idempotent re-runs),
else from the active/first data sheet.
## 2. LMG output columns (canonical order)
Exact LMG platform names and order (40 columns):
Relationship, Social Security Number, Status Field, Member Last Name, First Name,
Middle Initial, Gender, Date of Birth, Date of Hire, Member Street Address,
City, State, Zip Code, Phone Number, Email Address, Working Location,
Billing Division, Disabled?, Salary, Hours Worked per week, Occupation,
Dental Plan Selection, Dental Coverage Type, DHMO Provider Name,
Dental Prior Carrier Name, Dental Prior Carrier Effective Date,
Dental Prior Carrier Term Date, Dental Prior Carrier Ortho, Vision Plan Selection,
Vision Coverage Type, Basic Life Election, Primary Life Beneficiary,
Dependent Basic Life, Employee Voluntary Life, Spousal Voluntary Life,
Dependent Voluntary Life, STD, STD Class, LTD, LTD Class.
Notes: COV/COVERAGE → Coverage Type (tier EE/ES/EC/FAM/W); Plan/Select/Elect →
Plan Selection. Basic Life Election / Dependent Basic Life / STD / LTD read
**Enroll / Waive**. SSN is kept on the employee only. `Member Last Name` becomes
`Last Name` on the NCAM tab. Voluntary Life columns are omitted when blank/0/Waive
for everyone (§6).
## 3. Header normalization
- Remove parenthetical text: `RELATIONSHIP (EE, SP, CH)` → `RELATIONSHIP`.
- Unbalanced parens: if a stray `)` remains after stripping (e.g. `Status
(Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date and Event
Type)`), everything from the first `(` onward is instruction text — keep only
the text before the first `(` (→ `STATUS`).
- Uppercase, strip punctuation to spaces, collapse whitespace.
- Map normalized header to the LMG field. Priority: **user-confirmed HEADER MAP
entry > exact alias > high-confidence fuzzy match (≥85% word+character
similarity, auto-applied with a verify flag) > suggestion (50–85%, listed but
not applied) > unrecognized**. Exact matches never get flagged.
- **HEADER MAP tab:** every non-exact column is listed with a suggested field +
confidence and an editable `Map To` cell. Fill `Map To` with an LMG field and
re-run to include the column. Entries persist across runs (the tool learns).
- **No column leaves the LMG upload silently.** Any source column the tool does
not recognize — or recognizes but can't use because an earlier column already
filled that LMG field (e.g. both `Home Phone` and `Cell`) — is **flagged
WARNING on VALIDATION ISSUES** and **listed on Other Columns**, while its raw
values are always preserved on `RAW DATA`. The user decides; the tool never
drops a column without surfacing it.
## 4. Field rules
| Field | Rule |
|-------|------|
| Relationship | → `Employee` / `Spouse` / `Domestic Partner` / `Child`. Unknown (e.g. `Retiree`) = flag, keep original. |
| Last / First name | UPPERCASE. Missing = ERROR. |
| Middle Initial | First character only, UPPERCASE. |
| Gender | `Male`→`M`, `Female`→`F`. Else flag (blank/unknown = ERROR for enrollees). |
| Date of Birth | → `MM/DD/YYYY`. Accept slashes/dashes/ISO/2-digit-year/Excel serial. Unparseable or missing = ERROR. |
| Date of Hire | Employees only (blanked on dependents). → `MM/DD/YYYY`. |
| Prior carrier eff/term dates | → `MM/DD/YYYY`. |
| SSN | **Kept on the EMPLOYEE only** — removed from spouse / domestic partner / child. Digits only. If fewer than 9 digits, **pad leading zeros to 9** (INFO spot-check). More than 9 digits = ERROR. Employee missing SSN = ERROR. |
| Member Street Address | Merge Address 1/2/3 (and Addr/Street variants) with single spaces, UPPERCASE. |
| Member City | UPPERCASE. |
| State | Output header is **`State`** (not "Member State"). Full name → USPS 2-letter (`Texas`→`TX`). Invalid = flag; employee missing state = ERROR. **Required — never dropped.** |
| Member Zip Code | First 5 digits; restore dropped leading zero. |
| Phone Number | Digits only, 10 digits; strip leading country `1`. Invalid = flag + leave blank. **Multiple phone columns coalesce per row by priority: personal/home/generic → cell/mobile → work/office.** |
| Email Address | **Multiple email columns coalesce per row by priority: personal/home/generic → work/office.** |
| Annual Salary | Strip `$` and commas. Hourly → `rate × Hours Worked Per Week × 52`, using the member's **actual** weekly hours (e.g. 20 → `rate × 20 × 52`), default 40 only when hours blank/invalid (INFO flag). **Hourly vs annual is decided by a Compensation Type / Salary Type column when present** (Hourly/Annual), else by detecting "/hr" in the salary cell. Salary < $1,000 = flag as possible hourly. Non-numeric = flag. |
| Hours Worked Per Week | Use the value as given. Blank/invalid employee → `40` (fallback only). |
| Occupation | UPPERCASE; apply abbreviation table; max 25 chars (drop non-leading vowels, then truncate + flag). |
| Disabled? | Blank → `No`. Y/N variants → `Yes`/`No`. |
| Combined columns | A single `Name` / `City, State ZIP` / full-address column is split into the separate fields per §11c **before** the field rules above apply. |
### Abbreviation table (occupation)
SENIOR→SR, JUNIOR→JR, INFORMATION TECHNOLOGY→IT, SYSTEM(S)→SYS,
ADMINISTRATOR/ADMINISTRATION→ADMIN, SUPPORT→SUP, TECHNICIAN→TECH,
REPRESENTATIVE→REP, BUSINESS→BIZ, OPERATIONS→OPS, MANAGER→MGR, MANAGEMENT→MGMT,
ASSISTANT→ASST, DIRECTOR→DIR, ENGINEER→ENGR, SUPERVISOR→SUPV, DEVELOPER→DEV,
COORDINATOR→COORD, SPECIALIST→SPEC, GENERAL→GEN.
### Coverage vs Plan columns
- A column containing **COV / COVERAGE** (e.g. `Dental Coverage`, `Vision Cov
Level`) is the **Coverage Tier** — the election EE/ES/EC/FAM/W.
- A column containing **Plan / Select / Elect** (e.g. `Dental Plan`, `Vision
Select`, `Dental Election`) is the **Plan Elected** — which plan to enroll on.
- A Dental/Vision **Plan** column that holds only **one distinct value** is left
off the upload (the system auto-selects the single plan); it's noted on Other
Columns.
## 5. Coverage normalization
Normalize any tier value to one of: `EE`, `ES`, `EC`, `FAM`, `W`.
- Employee only / Self → `EE`.
- Employee + Spouse **or** Domestic Partner → `ES`.
- Employee + Child(ren) → `EC`.
- Employee + Spouse/DP + Child(ren) → `FAM`.
- Waive / Waived / Decline / None / Opt Out → `W`.
- Unrecognized → flag, keep original.
### LMG-specific
- Dental/Vision tier of `W` → output text `Waive with other coverage`.
- Blank **employee** dental/vision tier, **when the group carries that product**,
→ `Waive with other coverage` (LMG would otherwise treat blank as EE).
### NCAM-specific (reverse)
- `Waive with other coverage` → `W`.
- Blank employee dental/vision → `W` (NCAM defaults blanks to EE).
## 6. Life rules
- Primary Beneficiary: if an employee has Basic Life (election, coverage type, or
class present and not waived) and/or real Voluntary Life (employee/spousal/
dependent), and beneficiary is blank → `ON FILE`. Employee rows only.
- Employee & Spousal Voluntary Life: blank or waive → `0`. Employee rows only.
- **Dependent Voluntary Life: blank, `0`, or waive → `Waive`** (not `0`).
- A Voluntary Life column (Employee / Spousal / Dependent) is **dropped from the
LMG output entirely if it is blank or `0` for every row** — it only appears when
there are real elections. (Dropped columns are listed on Other Columns.)
- Spousal Voluntary Life found on a dependent row → flag INFO (roll-up to
employee is a future version; handle manually for now).
## 7. NCAM derivation
From LMG, employees only. `Member Last Name` → `Last Name`.
Columns: Last Name, Gender, Date of Birth, Dental Coverage Tier,
Vision Coverage Tier. **If STD or LTD coverage exists**, also include:
Annual Salary, Occupation, STD Election, STD Class, LTD Election, LTD Class.
Counts must reconcile to LMG.
## 8. Duplicates
Key = Last Name + First Name + Date of Birth + Gender. Duplicates are
**flagged** as WARNING in VALIDATION ISSUES — **never silently merged or
deleted**. An upload file must never silently drop an enrollee. The user
reviews and removes. (Family-unit-aware merge is a planned future version.)
## 8b. Special characters & extra validation flags
- **Special characters** anywhere in the census are replaced with a space; `#` →
` UNIT `. Exempt: date fields, Email Address, and numeric fields (SSN, Phone,
Zip, Salary, Hours, Voluntary Life).
- **Age 65+**: any member (employee or dependent) 65 or older is flagged.
- **Voluntary life not in even thousands** (e.g. 62,500) is flagged.
- **Family consistency** (flag only, never auto-fixed): a dependent listed but not
covered by any tier → flag; a tier that implies a dependent not listed (ES w/o
spouse, EC w/o child, FAM missing either) → flag (row-listing censuses only).
## 9. Validation severities
- **ERROR** — missing/invalid required data (no SSN, bad/missing DOB, missing gender). Fix before upload.
- **WARNING** — likely wrong but not blocking (odd salary, unknown relationship/tier, possible duplicate, over-long occupation, bad phone).
- **INFO** — FYI (e.g. spousal vol-life on a dependent row).
## 10. Guiding principle for variable carrier formats
Carriers, payroll vendors, and TPAs each format census files differently; for the
most part their columns match the alias logic above, but it varies. The tool
**auto-handles the normal patterns and FLAGS anything out of the norm for the end
user to decide — it never guesses silently.** When a structural detection isn't
confident, it does not transform; it flags the rows and passes them through. This
extends the no-silent-loss rule from values to structure.
## 11. Pivot pre-pass (IMPLEMENTED)
Runs BEFORE normalization, reshaping stacked/sideways layouts into one clean
enrollee row per person, then hands off to the existing pipeline. Transforms only
when confident; flags anything out of the norm.
- **Stage 0 — product-coded routing.** When a row has a generic value column
(Coverage Tier / Coverage Level / Coverage Type, Plan, Election, Class) plus a
product column (Plan Type / Product = Dental / Vision / Basic Life / STD / LTD),
the generic value is moved into the product-specific LMG field for that row's
product *before* the collapse: TIER→Dental/Vision Coverage Tier, PLAN→Dental/
Vision Plan Elected, ELECTION→Basic Life/STD/LTD Election, CLASS→Basic Life/STD/
LTD Class. Unknown products are left alone (flagged). Output stays
ancillary-focused — Medical/Medicare generic values are not routed/output.
- **Stage 1 — collapse stacked product rows.** Gated on a Product column
(Product / Coverage / Coverage Type / Plan Type / Benefit / Line of Coverage).
Groups rows by person (SSN + last + first + DOB), merges each person's non-blank
cells into one row (so the Dental row's tier, the Vision row's tier, etc. land on
one line). Conflicting non-blank values for the same person/column are flagged
WARNING, not silently overwritten. The existing duplicate check is the post-pivot
fail-safe.
- **Stage 2 — explode dependent columns.** Detects `Dependent N <field>` groups
(Name/SSN/DOB/Gender/Relationship, split or combined name; index N to any depth).
Each populated dependent becomes its own row under the employee, inheriting the
employee's address/city/state/zip/location to tie the family. Missing dependent
relationship → defaulted to Child + INFO flag. A comma-less combined name is split
"First Last" with an INFO flag to verify. (The dependent combined-name split
shares the same name parser as §11c — identical suffix and middle-initial rules.)
- If a file uses a single generic value column coded only by Product (no per-product
columns), it is left for manual handling rather than guessed.
## 11b. Family roll-up (IMPLEMENTED)
After the rows are built, families are grouped by **adjacency** — an Employee row
plus the Spouse/Domestic Partner/Child rows beneath it, until the next Employee.
Onto the employee row the engine rolls: spouse's voluntary life → Spousal
Voluntary Life; child's → Dependent Voluntary Life; dependent basic life →
Dependent Basic Life (those are blanked on the dependent rows). The employee's
Dental/Vision Coverage Type is **derived per product from who is enrolled in that
product** (a dependent who waived that product does NOT count) — spouse+child →
FAM, spouse → ES, child → EC. Upgrade-only: never lowers a stated tier.
Demographics stay on the dependent rows. Every roll-up is flagged INFO to verify.
**Only employee-line data is uploaded.** After the roll-up, spouse / domestic
partner / child rows have all election, employment, AND address/contact fields
blanked (coverage types, plans, life, disability, beneficiary, salary, occupation,
hire, location, street/city/state/zip, phone, email). Dependents keep only
Relationship, name, Middle Initial, gender, DOB, and Disabled?.
## 11c. Combined-column split — "data extrapolation" (IMPLEMENTED)
Some carriers consolidate several fields into ONE column. This split runs as the
**first stage of the pre-pass** (before §11 Stage 0 product routing, collapse,
and dependent explode, so person-keying sees the split First/Last), detects
combined columns by header + value shape, and extrapolates them into the
separate canonical fields. Per §10: transform only when confident; otherwise
flag and pass through untouched.
### Shapes
| # | Shape | Example values | Split into |
|---|-------|----------------|------------|
| 1 | Combined name | `Smith, John A` / `Jane Doe` | Member Last Name, First Name, Middle Initial |
| 2 | City/State/Zip | `Austin, TX 78701` / `Round Rock TX 78664-1234` | City, State, Zip Code |
| 3 | Full address | `123 Main St, Apt 4, Austin, TX 78701` | Member Street Address, City, State, Zip Code |
### Detection (header candidates + value-shape confidence)
Confidence = fraction of **non-blank** cells in the column that parse (full
scan). Thresholds: **60%** for a named combined header (`SPLIT_CONFIRM`),
**90%** for a value-shape-only trigger on a plainly-named column
(`SPLIT_STRONG`), **70%** for full address, which is always value-gated
(`SPLIT_ADDR`). Columns with fewer than 5 non-blank values require **100%**
parse for value-shape-only triggers. Between **30%** (`SPLIT_HINT`) and the
threshold → WARNING flag, **no transform** (the column passes through as
today). Below 30% → nothing.
- **Shape 1 — combined name.** Header candidates: `NAME`, `FULL NAME`,
`EMPLOYEE NAME`, `EMP NAME`, `MEMBER NAME`, `ENROLLEE NAME`,
`SUBSCRIBER NAME`, `PARTICIPANT NAME`, and other `* NAME` headers except
FIRST/LAST/MIDDLE/CARRIER/PLAN/PROVIDER/BENEFICIARY/PCP/OFFICE/FACILITY
names. **Guard:** if separate First AND Last Name columns exist, the combined
column is left alone (INFO note) — the explicit columns win.
- **Shape 2 — City/State/Zip.** Header candidates: `CITY STATE ZIP`,
`CITY ST ZIP`, `CSZ`, `CITY STATE`, `CITY AND STATE` (zip optional when the
header lacks ZIP). Also triggers on a plain `CITY`/`TOWN` header when ≥90% of
values are full `City, ST 12345` shapes (zip + valid state required).
- **Shape 3 — full address.** Candidate columns: `ADDRESS`, `FULL ADDRESS`,
`HOME ADDRESS`, `MAILING ADDRESS` and Address-column variants. Header alone
is never sufficient (an `Address` column is normally street-only): requires
≥70% of values to parse as street + city + valid state + zip with a
comma-delimited city. **Guard:** skipped entirely when separate City AND
State AND Zip columns exist; only the first qualifying column splits (other
address columns keep merging into the street per §4).
### Parsing rules
- **Name:** a trailing suffix (JR/SR/II/III/IV/V, with or without periods) is
detached and re-appended to the last name. Comma present → `Last, First
[Middle]` (reliable). No comma → guessed: 2 tokens = `First Last`; 3 tokens
with a 1-character middle = `First MI Last`; 3+ tokens otherwise = first word
is First Name, the rest is Last Name (protects `Mary Van Buren`); 1 token =
Last Name only + WARNING.
- **City/State/Zip and full address parse right-to-left:** trailing zip
(`12345` or `12345-6789`), then a trailing valid USPS 2-letter code or full
state name (longest match first — `DISTRICT OF COLUMBIA`), remainder = city
(full address: last comma segment = city, the rest = street). Values are
stored raw; the normal §4 field rules (UPPERCASE, USPS conversion, zip-5)
apply downstream.
### No silent loss
- The consumed combined column is removed from mapping but its raw values stay
on `RAW DATA` (Office Script) / the source file (siblings), and every
executed split is logged and flagged INFO with a parsed-row count.
- In a confidently-split column, a row that fails to parse keeps its **whole
original value** in the primary target field (City for shape 2, Member
Street Address for shape 3, Member Last Name for a one-word name) + WARNING.
- Comma-less name rows get ONE column-level INFO with a count ("split as
'First [MI] Last' — verify"), not a flag per row.
- Low-confidence columns (30%–threshold) are flagged WARNING and passed
through unchanged for the user to decide.
## 12. Roadmap (not yet implemented)
- **v2+** — duplicate merge (currently flagged), per-product dependent enrollment
for tier derivation, family linking by subscriber-SSN / sequence-number (today:
adjacency), beneficiary refinements.
- **v3** — richer NCAM column selection + reconciliation guarantees.
- **v4** — expanded SUMMARY (per-plan, per-tier, enrolled/waived/eligible).
---
### Changelog
- **v1.1** (2026-07-06) — added §11c combined-column split ("data
extrapolation"): combined Name, City/State/Zip, and full-address columns are
split into separate canonical fields; §4 combined-columns row; dependent
name split now shares the §11c name parser.
- **v1.0** (2026-06-29) — initial canonical spec, extracted from the LMG/NCAM
Office Script and the Copilot requirements transcript.
# Future-AI Reference Blurb
Paste this into any AI chat (Claude, Copilot, ChatGPT) when you need help
adjusting the tool. It gives the assistant the full context in one shot.
---
We are maintaining the **LMG / NCAM Census Translation Engine** — an Excel
**Office Script** (TypeScript) for Humana ancillary census processing. It
converts a carrier census into two outputs:
1. **LMG** — LaunchMyGroup enrollment upload format.
2. **NCAM** — employees-only quoting format derived from LMG.
**Delivery:** a single Office Script pasted into Excel → Automate tab → Run. It
operates on the open workbook. No backend, no install.
**Output workbook tabs:** `LMG` (always worksheet #1 — LMG uploads the first
sheet only), `NCAM`, `SUMMARY`, `VALIDATION ISSUES`, `PROCESSING LOG`,
`RAW DATA` (untouched original).
**Source of truth for columns:** the LMG platform field list and alias variants
live in `LMG_Column_Dictionary.xlsx` (CensaScrub project). Output columns follow
that dictionary's order and names.
**Key processing rules:**
- Headers: strip parenthetical text; map common variants to LMG field names.
- Relationship → Employee / Spouse / Domestic Partner / Child (unknown = flag).
- Names: Last/First UPPERCASE; Middle Initial = 1 char; Relationship Proper Case.
- Gender → M / F (else flag).
- Dates (DOB, hire, prior-carrier) → MM/DD/YYYY; handles slashes/dashes/ISO/
2-digit years/Excel serials.
- SSN: 9 digits, digits only; employee missing SSN = error.
- Address 1/2/3 merged → MEMBER STREET ADDRESS (UPPERCASE).
- State names → USPS 2-letter; invalid = flag.
- Zip → first 5 digits; restore dropped leading zero.
- Phone → 10 digits (strip leading country 1); invalid = flag + blanked.
- Salary: strip $ and commas; `$X/hr` annualized as rate × hours × 52
(hours default 40); salary < $1,000 flagged as possible hourly.
- Occupation: UPPERCASE, abbreviation table, max 25 chars (drop vowels, then
truncate + flag).
- Disabled? blank → No.
- Coverage tier → EE / ES / EC / FAM / W.
- LMG: waived dental/vision → `Waive with other coverage`; blank employee
tier (if the group carries that product) → `Waive with other coverage`.
- NCAM: convert those back to `W` (NCAM defaults blanks to EE).
- Primary Beneficiary: employee with Basic/Voluntary Life but blank bene → `ON FILE`.
- Voluntary Life: employee blank/waive → 0.
**Pre-pass (reshaping, runs before normalization):**
- Stage 1 collapses stacked product rows (same person across many rows, one per
coverage line) into one row — gated on a Product column; groups by SSN+name+DOB;
merges non-blank cells; flags value conflicts.
- Stage 2 explodes `Dependent N <field>` columns into Spouse/Child rows under the
employee, inheriting address/location; flags defaulted relationships and guessed
name splits.
- Reshapes only when confident; flags anything out of the norm. Governing rule:
auto-handle the normal patterns, flag the rest, never guess silently.
**Validation:** ERROR (missing/invalid required), WARNING (likely wrong),
INFO (FYI). Columns: SEVERITY, ROW NUMBER, MEMBER NAME, COLUMN, VALUE, ISSUE,
RECOMMENDED ACTION.
**Deliberate deviation from earlier spec:** duplicates (same last+first+DOB+
gender) are **flagged, not silently merged** — an upload file must never drop an
enrollee silently. The user reviews and removes.
**NCAM columns:** Last Name, Gender, Date of Birth, Dental Coverage Tier,
Vision Coverage Tier; plus Annual Salary, Occupation, STD Election, STD Class,
LTD Election, LTD Class **only if** STD or LTD coverage is present.
**Planned next versions (not yet built):**
- V2: family-unit assignment, duplicate merge, voluntary-life roll-ups,
beneficiary logic refinements.
- V3: richer NCAM column selection and reconciliation guarantees.
- V4: expanded SUMMARY (per-plan, per-tier, enrolled/waived/eligible).
**When I bring you a problem,** I'll paste a real example: the case, the column,
the source value, and the wrong output. Help me adjust the matching script
(`LMG-NCAM-Census-Engine.ts`) — give me the exact edited code block, not
pseudocode.
Duplicates are flagged, not deleted — an upload file should never silently drop an enrollee. Family roll-up (deps → employee line) and product-coded coverage routing are built in.