Update to Patient Data Analysis

{
  "description": "Combine patient records from multiple city/location sheets into one source, build a pivot table counting HIV-positive patients with blood group O by location, and add a linked column chart with a city slicer filter.",
  "included_files": [],
  "name": "patient-data-analysis",
  "skill_md_contents": "---\r\nname: patient-data-analysis\r\ndescription: Combine patient records from multiple city/location sheets into one source, build a pivot table counting HIV-positive patients with blood group O by location, and add a linked column chart with a city slicer filter.\r\n---\r\n\r\n## When to use\r\nUse when the workbook has one patient-register sheet per city/location (e.g. Ibadan, Lagos, Abuja), all with the same headers, including **HIV Status** and **Blood Group**, and the user wants:\r\n- a pivot table built from **all** location sheets together, and/or\r\n- a chart of HIV-positive patients with blood group O (O+ and O−) that can be filtered by city.\r\n\r\nAlso use it when the user asks for the same analysis with a different criterion (e.g. \"HIV negative and blood group A\"). Change the flag formula in Step 2 to match.\r\n\r\n## Expected sheet layout (input)\r\nEach location sheet has a header row in row 1 and one patient per row, for example:\r\n`Patient Name | Age | Occupation | Gender | HIV Status | Blood Group | Date of Test`\r\n- HIV Status values: `Positive` / `Negative`\r\n- Blood Group values: `O+`, `O-`, `A+`, `B-`, `AB+`, etc.\r\n\r\n## Step 1: Discover the location sheets\r\n- Use execute_office_js to list the worksheets. For each one, load the used range and row 1.\r\n- Treat a sheet as a location sheet if its headers include both \"HIV Status\" and \"Blood Group\". Skip \"All Patients\", \"HIV Pivot\", \"Data Sources\" and any sheet without those headers.\r\n- Record each sheet's name, last data row (from `getUsedRange().rowCount`) and the column letter of every header. **Don't assume column positions**: find \"HIV Status\" and \"Blood Group\" by header text.\r\n- If the sheets' headers differ, stop and tell the user which sheet is different.\r\n- Done when: you have a list of {sheetName, lastRow, headerMap} for every location sheet.\r\n\r\n## Step 2: Build the combined \"All Patients\" source sheet\r\nA pivot table can only use one contiguous range, so combine the location sheets onto one sheet with links. Don't paste static values.\r\n- If \"All Patients\" already exists, ask before replacing it. Otherwise create it with `worksheets.add(\"All Patients\")`.\r\n- Header row: `Location`, then every source header in order, then a flag column `HIV+ & Blood Group O (1=Yes)`.\r\n- For each location sheet, for rows 2..lastRow write:\r\n  - Column A: the sheet name as text (e.g. `Ibadan`).\r\n  - Columns B onwards: link formulas to the source cell, e.g. `=Ibadan!A2`, `=Ibadan!B2`, …\r\n  - Flag column: `=IF(AND(<HIV col><r>=\"Positive\",LEFT(<Blood col><r>,1)=\"O\"),1,0)`, where `<r>` is the row on All Patients (e.g. `=IF(AND(F2=\"Positive\",LEFT(G2,1)=\"O\"),1,0)`).\r\n- Write all formulas in one `range.formulas = [...]` call. Build the 2D array in JS.\r\n- Formatting: header row bold, white text on navy `#1F3864`, wrap text. Linked columns in green `#008000` (cross-sheet links). Date column `dd-mmm-yyyy`. Centre the numeric/code columns. Autofit columns A to the last linked column, set the flag column width to about 110. Freeze row 1.\r\n- Done when: the row count on All Patients equals the sum of all location data rows, and a spot-check of the first and last rows shows the right names and 0/1 flags.\r\n\r\n## Step 3: Create the pivot table\r\n- Create (or reuse) a sheet named \"HIV Pivot\". Put a bold 13pt title in A1: `HIV-Positive Patients with Blood Group O (O+ / O-) — all locations`.\r\n- `pivotTables.add(\"HIV_O_Pivot\", <All Patients A1:lastCol lastRow>, \"HIV Pivot\"!A3)`\r\n- Row field: **Location**.\r\n- Data field: the flag column, `summarizeBy = sum`, renamed `HIV+ & Blood Group O`.\r\n- Don't add a \"Total Patients\" count to this pivot. A second series on the chart dwarfs the HIV+ O bars. If the user wants totals, build a separate pivot.\r\n- After `context.sync()`, **re-read `pt.rowHierarchies`** and confirm it is `[\"Location\"]`. The row field has been seen to change to another field (e.g. Age). If it isn't Location, remove the wrong field and add Location.\r\n- Done when: `pt.layout.getRange()` shows one row per city plus Grand Total.\r\n\r\n## Step 4: Cross-check the count\r\n- In a temporary cell on All Patients, write `=COUNTIFS(<HIV col>2:<HIV col><last>,\"Positive\",<Blood col>2:<Blood col><last>,\"O*\")`, read the value, then clear the cell.\r\n- Done when: it equals the pivot's Grand Total. If not, investigate before continuing.\r\n\r\n## Step 5: Create the chart linked to the pivot\r\n- On \"HIV Pivot\", add a chart from the pivot's body range (header + city rows, **excluding the Grand Total row**, e.g. `A3:B6`):\r\n  `ws.charts.add(Excel.ChartType.columnClustered, range, Excel.ChartSeriesBy.columns)`\r\n  Because the source is a pivot range, Excel makes it a PivotChart that follows the pivot's filters.\r\n- Settings:\r\n  - name `HIV_O_Chart`, title `HIV-Positive Patients with Blood Group O by City`\r\n  - legend hidden\r\n  - series fill `#1F3864`, data labels on\r\n  - value axis title `No. of Patients`, minimum 0, majorUnit 1\r\n  - category axis title `City`\r\n  - position: `top = 45, left = 330, width = 420, height = 280`, to the right of the pivot so they don't overlap.\r\n- Done when: the chart has exactly one series, and its category values are the city names.\r\n\r\n## Step 6: Add the city filter (slicer)\r\n- `context.workbook.slicers.add(pt, \"Location\", ws)`: name `City_Slicer`, caption `Filter by City`, `left = 760, top = 45, width = 150, height = 150`.\r\n- The PivotChart's own **Location** field button also filters. Mention both to the user.\r\n- Test it: `slicer.selectItems([\"<one city>\"])`, read the chart series values and the pivot layout, and confirm they show only that city. Then run `slicer.clearFilters()`.\r\n- Done when: filtering changes both the pivot and the chart, and the slicer is cleared again.\r\n\r\n## Step 7: Visual check and report\r\n- Render `HIV Pivot!A1:P22` with `getImage()` + `attachImage` and confirm that the pivot, chart and slicer don't overlap, that the bars are labelled, and that no second series is present.\r\n- Report to the user:\r\n  - the per-city counts and the grand total\r\n  - where each thing lives: All Patients, and HIV Pivot for the pivot, chart and slicer\r\n  - how to filter\r\n  - that edits to existing rows show up after right-clicking the pivot and choosing **Refresh**, but new patients added below a sheet's current last row need All Patients extended (re-run Steps 1–2)\r\n\r\n## Conventions\r\n- Follow the user's hardcoded-input preference: numeric inputs are written with a leading `=` (e.g. `=34`, `=DATE(2026,1,14)`).\r\n- Never compute the counts in JS and paste them. The counts must come from the pivot and the flag formulas.\r\n"
}