A corporate gift duplicate audit should connect each request, invitation, order and remedy to the same approved gifting occasion. Count active authorizations separately from delivered parcels, preserve original identifiers, and review replacement lineage before deciding that two records represent an accidental second gift. This workbook method is a bounded review aid: it does not cancel orders, identify people automatically or authorize another shipment.
Start with the question the register can answer
Download the English workbook and English CSV field dictionary, version 2 revised October 28, 2026. The workbook contains fictional examples and fifty input rows per transaction sheet. Test it in your intended spreadsheet application before using approved operational exports.
The useful question is not whether a recipient appears twice. It is whether the same approved opportunity has more than one active authorization, an unexplained second original order, or multiple deliveries requiring a documented remedy. A person can receive a welcome gift and a service anniversary gift legitimately. A replacement can also be legitimate without creating another entitlement. Conversely, two different sponsor names do not necessarily justify two gifts for the same occasion.
Define the campaign, occasion and recipient token before collecting transaction rows. The campaign identifies the bounded program; the occasion identifies the approved opportunity within it; the token identifies the recipient through an authorized source. These three values form a review key. An order family is different: it connects an original fulfillment attempt and its replacements. Never use a delivery address as the family key, because colleagues can share an office and a recipient can move.
This 2027 planning guide describes an original editorial method checked on October 28, 2026. Its examples are hypothetical, not customer incidents or measured industry results. The workbook's 2027 version label is not a claim of future verification. The method assumes one active authorization per defined opportunity; a program allowing multiple gifts must obtain a revised rule and test it before using the results. A blank flag is not permission to ship.
Keep the policy decision outside the spreadsheet. The campaign owner decides entitlement, finance decides spending authority, operations owns transaction reconciliation, and a separate reviewer evaluates exceptions. Privacy and legal specialists determine applicable obligations. If the owner cannot define an occasion consistently, fix that policy ambiguity before deduplicating the data. Otherwise, a clean-looking register can hide an incorrect business decision.
Build a minimal evidence chain across eight worksheets
Use the Read Me sheet to record the scope, version, source cutoff, responsible operator and review limitations. Requests holds the sponsor's proposed opportunity and approval reference. Invitations holds the actual invitation identifier, the linked request and active-authorization state. Orders holds the original order identifier, invitation, family and replacement relationship. Remedies records the owner's recipient-safe action and evidence rather than rewriting the transactional history.
The Rules sheet explains the grouping and validation logic. Exceptions helps the reviewer organize findings and next actions. QA contains synthetic checks that establish whether the expected examples still behave as intended. These sheets serve different purposes: the QA examples do not certify the completeness of a live export, and an exception note does not alter a formula's result. Keep a separate source manifest when several systems supply records.
Field dictionary: proposed inputs and their evidentiary roles.
| Record | Minimum identity | Evidence needed | Decision owner |
|---|---|---|---|
| Request | Request, campaign, occasion and recipient token | Sponsor, approval reference and protected evidence pointer | Campaign owner |
| Invitation | Original invitation and matching request | Explicit active state and actual sent date | Operations |
| Order | Order, invitation and family | Original-order link for replacement, reviewer and delivery evidence | Fulfillment owner |
| Remedy | Remedy and existing family | Decision owner, date, agreed action and evidence | Remedy owner |
The working area contains fifty input rows per transaction sheet, rows six through fifty-five. Do not assume that appending a fifty-first transaction extends formulas or the review population. For a larger program, use a separately validated expansion or a controlled query-based register. Record imported row totals, rejected rows and the coverage boundary so a reviewer can distinguish an empty finding list from an incomplete population.
Import source identifiers as text, preserving leading zeros. Retain original values alongside any normalized comparison key. Do not replace a source reference with the spreadsheet row number: sorting would change its meaning. The companion UTF-8 CSV is a field dictionary, not a transaction import file, recipient list or executable formula package. Review the recipient CSV import guide when designing a separate transfer into an operational system.
Understand the calculation before trusting its color
For invitations, normalize the campaign, occasion and token by trimming surrounding spaces and converting case for comparison. The compound key uses length-prefixed components, so separator characters within a component cannot make two different identities collapse into one key. Preserve the source identifiers for investigation; normalization is a comparison rule, not authority to edit the original system. A reviewer must first confirm that case-insensitive matching is appropriate for the source's identifier contract.
The active count measures matching rows whose active-authorization value is the numeric value one. The inactive value is numeric zero. Empty cells and text that looks like a number require correction; they are not harmless substitutes. A missing invitation identifier remains an input defect even when the campaign, occasion and token are sufficient to contribute to a matching count. Hiding that row from the count could conceal the very duplicate under investigation.
The current method rejects identifiers containing the reserved characters asterisk, question mark or tilde rather than interpreting them as wildcard criteria. Do not silently remove these characters to make a row pass. Ask the source owner for an approved identifier mapping, preserve the original reference in restricted evidence, and test the mapping for collisions. A supported source contract is more reliable than an improvised text cleanup.
The invitation must reference exactly one request with the same campaign, occasion and token. A valid-looking key cannot compensate for a missing parent, a duplicated request identifier or contradictory identity. Sent dates must be positive numeric spreadsheet dates. Agree the source date convention before loading records; a formatted text date can look convincing without being usable by the checks. Preserve the actual event time and time zone in source evidence when the workbook's date field is less precise.
Order review then applies a different test. Each order belongs to a family and one invitation. A replacement must identify a unique original order in the same family and invitation, cannot point to itself, and needs a reviewer, approval and evidence. Multiple original orders remain a review finding. More than one delivered order in a family requires reconciliation even if the replacement was approved. The calculation detects a condition; the owner decides its remedy.
Hypothetical case one: two sponsors, one occasion
Imagine a US employer preparing one welcome gift for each newly hired employee. Human resources submits request R101 and a local manager submits R102 for the same campaign, welcome occasion and masked recipient token. Both approvals are recorded, and operations discovers invitations I101 and I102 marked active. The sponsor codes differ, but the approved policy allows only one welcome opportunity. This is a synthetic training example, not a reported customer event.
The operator first preserves both request and invitation records. They confirm that the two tokens resolve to the same authorized recipient in the protected source, without copying an address into the audit workbook. Case and surrounding-space differences are normalized for comparison. Both invitation rows should show two active authorizations. The reviewer then checks whether either invitation has already produced an order, whether a recipient has acted on it, and what the actual cancellation boundary permits.
The campaign owner selects the surviving authorization using the approved policy and evidence, not whichever row happens to appear first. If the second invitation can be withdrawn, operations requests that specific withdrawal and records the destination acknowledgment. Until the outcome is known, do not change the source state to inactive merely because someone sent an email asking for cancellation. A timeout leaves an unknown operation, not a failed operation that can safely be repeated.
Once the destination confirms the outcome, preserve the inactive invitation with its original identifier and supporting receipt. Recalculate the workbook from the corrected source extract. Acceptance requires one active authorization, the chosen approval reference, the withdrawal evidence, and a check that no second order or recipient promise remains unresolved. The reviewer signs the exception decision separately from the operator's data correction.
Now change only the second occasion to an approved anniversary opportunity. The test should no longer treat the two invitations as duplicates of the same opportunity. Restore the welcome occasion and remove one invitation identifier: an input defect must remain visible while the active count still reflects two matching authorizations. These boundary tests distinguish a defensible rule from a cosmetic removal of repeated names. If the employer actually authorizes two welcome gifts, revise the rule explicitly instead of inventing a second occasion code.
Hypothetical case two: a replacement and a late original
Consider a fictional client program in which original order O201 is reported missing. Support opens a case, the fulfillment owner investigates, and an authorized reviewer approves replacement O202. Both orders link to invitation I201 and family F201. O202 identifies O201 as its original order. The replacement decision, reviewer code and evidence reference are recorded before the replacement is treated as an approved exception.
Initially, O201 is not confirmed delivered and O202 is delivered. The family has one delivered order. This does not prove the original was destroyed or that the support case can be forgotten. Preserve the uncertainty and the agreed follow-up date. A replacement approval explains why a second attempt exists; it does not retroactively cancel the original shipment or prove that every associated cost has been reconciled.
Suppose the original subsequently arrives. Update its delivered state and actual delivery date from reliable evidence. The family now has two deliveries and must remain a reconciliation finding. Do not mark the original undelivered to suppress the warning. Do not erase the replacement relationship, either: it remains relevant to explaining the decision that was reasonable at the earlier cutoff. The register should tell both parts of the story.
What an approved replacement can and cannot override
An approved replacement explains an additional fulfillment attempt only when the original is unique, belongs to the same family and invitation, and is supported by a named reviewer and evidence. It cannot override missing parent records, a self-reference, multiple originals or the fact that two orders were delivered. A remedy marked closed is an owner's recorded decision; it does not clear the calculated finding or rewrite source history.
The remedy owner chooses a recipient-safe response under the actual agreement. Depending on the facts, that might mean allowing the recipient to retain the extra parcel, arranging an agreed return, or coordinating a different resolution. The spreadsheet does not establish a right to charge the recipient, require a return or issue a refund. Record communication, cost responsibility, any required inventory adjustment and the evidence that the chosen action occurred. Avoid making the recipient repeat internal investigation details unnecessarily.
Acceptance requires the two delivery records, the original replacement approval, the remedy decision and independently checked execution evidence. A closed remedy can coexist with a historical two-delivery finding: the purpose is an explainable resolved exception, not an artificially perfect data set. Use the incident postmortem guide if repeated duplicates reveal a shared process failure beyond this individual family.
Protect recipient data without losing traceability
For US operations, the Federal Trade Commission's business guidance supports limiting personal information and access to legitimate business needs. Apply that principle by keeping a masked token in the audit copy and the identity mapping in an appropriately restricted source. Tokenization alone does not make information anonymous when someone can reconnect it to a person.
Separate permissions for the source export, the audit workbook, the identity mapping and the evidence repository. A reviewer may need to confirm a delivery event without seeing a home address or personal message. A fulfillment partner may need delivery details without receiving internal sponsor disagreements. Use references that authorized reviewers can resolve, and test access with the intended role before accepting an evidence pointer as usable.
Choose retention with the responsible privacy, records and legal owners. The method proposes no universal deletion period, state-law exemption or cross-border permission. Record why transaction evidence is retained and how obsolete working copies are removed. A legal hold or accounting requirement may affect the decision, but copying every attachment into every spreadsheet is not a retention strategy. When a source record is corrected, preserve a bounded audit trail rather than retaining unrestricted personal data indefinitely.
Keep real people out of training and regression files. Synthetic tokens, dates and order references are sufficient to test the duplicate logic. Before sharing a workbook for troubleshooting, inspect hidden sheets, comments, links and document properties as well as visible cells. Treat the distribution copy as a separate artifact with a named owner and version. A filename containing “masked” does not prove that the actual bytes contain no personal information.
Test failures, not just the two happy paths
Begin with a clean copy and a recorded expected-result table. Test one valid invitation, two active invitations for one key, a separate legitimate occasion, mixed case and surrounding spaces, a blank token, and a missing invitation identifier. Test a duplicate request identifier and an invitation whose token disagrees with its request. Each failure should produce an understandable finding while preserving the row for investigation.
For orders, test an ordinary original, an approved replacement, an unapproved replacement, a missing reviewer, a missing original, a self-reference, a cross-family original and two original orders in one family. Test two delivered orders after the late-original scenario. Blank numeric flags must not act like zero. A delivered flag without a usable delivery date, or a date attached to an undelivered order, should require correction rather than a guess.
After a formula change, recalculate, export, reopen and recalculate again. Compare expected and observed results and scan for formula errors. Inspect every worksheet in every distributed language, including column widths, visible headings, dates, wrapped instructions and result labels. A workbook whose formulas calculate but whose localized instructions are invisible has not passed visual acceptance. Test the actual exported binary rather than relying only on the authoring session.
Record the tested file's version, byte count and fingerprint, the reviewer, the test cutoff and unresolved limitations. Native application behavior can differ from an automated calculation environment, so do not claim universal compatibility without testing the intended application. If a test changes an input, restore the baseline or save a clearly separate test copy. Never distribute a file with a hidden mutation that makes the published example appear to work.
Choose the operating model and the acceptance boundary
Make the review reproducible before making it recurring. The operator records which exports were received, their original creation times, the covered campaigns and any excluded records. The reviewer compares those totals with the workbook population and resolves differences before evaluating the findings. If a supplier exports only current active orders, request the authorized history needed to see cancelled and replaced attempts; do not describe a current-state snapshot as a complete transaction history.
Set a practical escalation rule for missing evidence. A family with an unknown shipment outcome needs an owner, a specific lookup or supplier inquiry, and a dated next check. It should not receive an automatic replacement simply because the review meeting is ending. Likewise, a suspected duplicate with no cancellation receipt stays unresolved even if the intended decision is clear. Track requested actions and observed outcomes separately so the next operator does not repeat a potentially successful operation.
For a pilot, have someone who did not prepare the file reconstruct both hypothetical cases using only the workbook and permitted evidence references. Ask them to identify the surviving authorization, explain the replacement lineage, locate the delivery contradiction and state the unresolved decision. If they need undocumented verbal explanations, improve the field definitions or evidence references. This is a usability and control test, not a claim of external audit assurance or a benchmark of error reduction.
A bounded workbook suits a campaign whose population fits the tested range and whose exceptions have accountable human owners. A controlled database or query process is more appropriate when concurrent edits, larger populations or repeated feeds require stronger access and change controls. Manual review can suit a very small population, but still needs stable identifiers and retained decisions. These are operating alternatives, not rankings or evidence that one product prevents every duplicate.
Before choosing software, ask who controls identity mapping, how original event identifiers are exported, what a timeout means, and whether a cancellation request has an authoritative receipt. Demonstrate a duplicate invitation and a late-original replacement with synthetic data. Evaluate the same cases against each option, including the work required to reconcile exceptions. A dashboard showing one recipient does not prove that only one order exists downstream.
Close the audit cycle with a frozen population and an exception disposition, not a promise that duplicates can never recur. The handoff should identify the source cutoff, coverage, outstanding unknown operations, owners and next checks. Keep resolved exceptions visible with their evidence. Refresh the method quarterly and whenever the entitlement policy, identifier contract, export format or fulfillment process changes; the calendar alone does not validate a changed rule.
The practical outcome is a reproducible decision: another authorized reviewer can trace one opportunity through every attempt and explain why an extra record was rejected, retained or remedied. If the next step is choosing a gifting execution service, Giftpack's campaign guidance can help frame a discussion about recipient choices and program rules. Confirm the contracted scope; this independent workbook is not a claim of native duplicate-audit functionality, and Giftpack does not replace the organization's legal, privacy or approval decisions.