Cleaning a beneficiary list, formulas and pivot table

(a) =COUNTIFS(B2:B21,"B",C2:C21,"F",F2:F21,"Y") (French: NB.SI.ENS): result 4 on the raw file, but 3 actual households, because M-003 is counted twice.

(b) =SUMIFS(H2:H21,B2:B21,"A") (SOMME.SI.ENS): 50,000 + 70,000 + 60,000 + 70,000 + 60,000 + 30,000 = 340,000 (no duplicates on site A).

(c) =XLOOKUP(B2,Sites!$A$2:$A$4,Sites!$C$2:$C$4,"Unknown site") or =VLOOKUP(B2,Sites!$A$2:$C$4,3,FALSE): absolute references and exact match are mandatory.

(d) =COUNTIF($A$2:$A$21,A2)>1 returns TRUE for every occurrence of a duplicate ID; to flag only the second occurrence: =COUNTIF($A$2:A2,A2)>1. The same formula on column G identifies shared phones.

(e) Amount due in I2: =MIN(D2,7)*10000; difference in J2: =H2-I2, then filter on J not equal to 0.

Certain errors: rows 9 and 19: exact duplicates of M-003 and M-008, i.e. 30,000 + 50,000 = 80,000 paid twice; row 5: M-004, 9 people, paid 90,000 instead of 70,000 (cap), i.e. + 20,000; row 15: M-013, 4 people, paid 45,000 instead of 40,000, i.e. + 5,000.

Certain overpayment: 80,000 + 20,000 + 5,000 = 105,000 FCFA, i.e. 10.6% of the 995,000 paid on the extract. Amount due for the 18 unique households: 890,000.

To be verified: row 12: M-010, household of 25 people, an outlier (probably 2 or 5); the cap limits the payment to 70,000, but if the actual size is 5, the overpayment is 20,000; rows 5 and 16: M-004 and M-014 share phone 8812: possibly a household split to receive two transfers, or simply a shared phone, to be checked in the field without accusing anyone; row 21: M-018, age missing, to be completed.

Not an error: row 7: M-006, 16-year-old head of household: a child-headed household, a protection situation to be referred to the child protection actor, without deleting the row.

Corrections are made in a copy, with a change log; duplicates are not "deleted" from the source file but flagged, to keep a record of payments.

Comments: (1) 13 out of 18 households (72.2%) are headed by a woman: payment arrangements (hours, distance, access to phones) must be adapted to them. (2) Site C has 47 people for 6 households, but this total includes the outlier size of 25: until it is verified, the population figures for site C are not reliable. It can also be noted that site B received 360,000 on the raw file against 260,000 due: this is the site where the errors are concentrated.

• Structure: raw data in an unmodified sheet, formatted as a structured table; a parameters sheet (amount per person, cap) instead of values hard-coded in formulas; a correction log sheet; separate output sheets.
• Data entry: data validation (drop-down lists for site, sex, Y/N; size as a whole number between 1 and 20; age between 10 and 110 with an alert below 18); conditional formatting of duplicate IDs and phones.
• Automatic checks: "amount due", "difference" and "duplicate" columns; a data quality dashboard (number of duplicates, differences, missing values) to be reviewed before each payment.
• Outputs: pivot tables fed by the structured table, refreshed every month; or Power Query to import, deduplicate and merge the monthly files in a reproducible way.
• Governance: file naming and versioning, restricted access rights, validation of the list by a second person before it is sent to the payment agent.

I do not send the named list, and certainly not through a messaging service that the organisation does not control. The personal data of displaced people are sensitive: in a conflict setting, a list with names, sites and phone numbers can expose people to risks (targeting, pressure, extortion). The principles of data responsibility (minimisation, purpose limitation, security, sharing based on an agreement) apply.

I first try to understand the real need: if the authority wants to know how many households are being assisted and where, I immediately offer aggregated data (by site, by sex, by age group), which meet most coordination needs. If a legal basis or a data-sharing agreement exists and requires individual data, they are transmitted in accordance with that agreement, with the minimum number of fields, through a secure channel and after approval by senior management and the data protection officer. I explain the risks to people and to the organisation to the head of office, and I record the decision.