I85PREP
⌘ K
BSCB Clerk study materials
BSCB Clerk study resources

The Duplicate UTR Formula and the Two-Hour Suspension Email

BSCB Clerk · Put your knowledge to work in context.

All case studies

The Duplicate UTR Formula and the Two-Hour Suspension Email

At a cooperative bank's reconciliation desk, 3,860 UTR numbers imported from the RTGS/NEFT portal sit in column A (A2:A3861) of a spreadsheet, with the corresponding amounts in column C. Some UTRs arrived from the portal as text and some were retyped by hand, and a few carry trailing spaces. The clerk types =IF(COUNTIF($A$2:$A$3861,A2)>0,"Duplicate","OK") into B2 and fills it down; every single row now reads “Duplicate”. He concludes the portal data is corrupted and proposes to delete 1,900 rows before the day's proof is signed. Meanwhile an email reaches the branch's generic mailbox from “[email protected]” warning that the branch's RTGS access will be suspended within two hours unless the recipient clicks a link and re-enters the portal user ID and the OTP just sent, “for KYC verification”. A colleague has already clicked the link and typed the user ID; the OTP field is still open on his screen.

1. Explain why every row is flagged and give the corrected formula. What else must be checked before any row is believed to be a duplicate?

Read the worked answer

Why every row is flagged: COUNTIF is counting the row against the full range that includes the row itself. Every UTR matches itself exactly once, so every count is at least 1 and the condition “>0” is always true. The test is structurally wrong - it is not the data that is corrupt.

Correct test: =IF(COUNTIF($A$2:$A$3861,A2)>1,"Duplicate","OK")

Second problem - trailing spaces. A hand-retyped “R9202401 ” with a space will not match “R9202401” for counting purposes. Clean the key first, in a helper column: =TRIM(CLEAN(A2)), then count on that column. A robust alternative that ignores stray spaces is: =IF(SUMPRODUCT(--(TRIM($A$2:$A$3861)=TRIM(A2)))>1,"Duplicate","OK")

Before deleting anything: a repeated UTR is not automatically an error. The same reference can legitimately appear on a return leg, a reversal and re-presentation, or on a debit and its matching credit. Each flagged row must be matched against amount, value date and direction in columns C and beyond. The 1,900-row deletion must not happen on the strength of one formula.

2. What are the warning signs in the email, and what must be done in the next few minutes? Assume the OTP has not yet been submitted.

Read the worked answer

Warning signs - all four classic ones are present:

  • Lookalike domain. “npci-helpdesk.org” is not the NPCI domain; a regulator or network operator does not use a private help-desk domain for member-bank communication.
  • Manufactured urgency. “Suspended within two hours” is designed to stop the recipient thinking.
  • A request for secrets. No genuine NPCI or RBI communication asks for a portal user ID, password or OTP. The OTP alone is enough to authorise a transaction.
  • An unsolicited link taking the user to a credential-capture page.

Immediate actions:

  1. Tell the colleague to close the OTP page without submitting - the OTP is the last barrier and it is still intact.
  2. Do not use the same machine to change anything; from a clean, known-good device, reset the portal password of the affected user ID and terminate all active sessions.
  3. Call the bank's IT/CISO and the fraud incident-response team now - this is a reportable security incident, whatever the outcome.
  4. Pull the portal login and transaction logs for the last 24 hours and check for any maker entry or beneficiary added.
  5. Preserve the email with full headers as evidence; do not delete or forward it to colleagues as a curiosity.
  6. Warn the other branches through the usual channel - the same mail is likely to have gone to all of them.

3. The clerk still wants to strip the 1,900 “duplicates” out of the master file before the proof is signed. Set out the safe procedure and the checks that must accompany it.

Read the worked answer

Safe procedure:

  1. Work on a copy. Keep the portal download untouched and date-stamped as the source of record; all cleaning happens on a working copy.
  2. Normalise first. Build a helper column with =TRIM(CLEAN(A2)) and recalculate the duplicate flag on it, so text-formatted and space-padded entries are compared on equal terms.
  3. Substantiate each hit. A row is a true duplicate only if UTR, amount, value date and debit/credit direction all match. Use the count of matching rows rather than eyeballing.
  4. Reconcile before and after. Record the row count and SUM(C2:C3861) before deletion, and confirm both the expected row count and the expected total afterwards. If the total moves by an amount you cannot explain, stop.
  5. Delete, don't overwrite. Delete entire rows only after the flag is verified, so no formula range shifts; alternatively use Data ▸ Remove Duplicates on a sorted copy and compare the result with the manual list.
  6. Protect and document. Lock the sheet, note who cleaned what and when, and retain the copy so the proof can be reconstructed if the reconciliation is questioned.

A deletion from a master reconciliation file without these steps is an audit finding waiting to happen - and in this case it would have removed live entries based on a counting error.