Right-only keys are missing
Build the key population from both source lists.
Reconcile grouped totals from two exports
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return each nonblank ID occurring in A2:A9 or D2:D11 once, uppercased, in first-seen order across A then D. Report its signed numeric total from B, total from E and A minus B. Match printable ASCII IDs literally ignoring case; spaces and leading zeroes remain significant. Repeated IDs are aggregated, missing-side totals are numeric zero, and zero-net keys remain. If both lists are empty, return No keys.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:J22. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Keep the union of both ID lists, combine signed repeated rows on each side, then report both totals and their difference.
THE QUICK ANSWER01
Keep the union of both ID lists, combine signed repeated rows on each side, then report both totals and their difference.
ƒx
=LET(joined,VSTACK(A2:A9,D2:D11),keys,UNIQUE(FILTER(UPPER(joined),joined<>"","")),booked,MAP(keys,LAMBDA(key,SUMPRODUCT((UPPER(A2:A9)=key)*B2:B9))),received,MAP(keys,LAMBDA(key,SUMPRODUCT((UPPER(D2:D11)=key)*E2:E11))),IF(SUMPRODUCT(--(joined<>""))=0,"No keys",HSTACK(keys,booked,received,booked-received)))| Normalized ID | Total A | Total B | A minus B |
|---|---|---|---|
| EAST | 125 | 125 | 0 |
| WEST | 50 | 50 | 0 |
| NORTH | -10 | -10 | 0 |
| ONLY-A | 7 | 0 | 7 |
| ONLY-B | 0 | 20 | -20 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Why the union matters
The original report includes EAST, WEST, NORTH, ONLY-A and ONLY-B. EAST totals 125 on each side. ONLY-A keeps 7 and zero; ONLY-B keeps zero and 20, producing -20 in the residual column.
A zero total is still a key
Rows of 10 and -10 total zero, but their ID still exists in the source. Keep that key, both totals and the residual. Filtering on a nonzero amount would discard evidence.
First-seen order is explicit
The report follows normalized IDs encountered in A first, then new IDs from D. It does not use a locale-dependent alphabetic sort. Reordering the sources may change report order while leaving each keyed total unchanged.
A bounded comparison
Each side permits blank rows. A blank ID may have a blank or numeric-zero amount; other amounts without an ID are left ungraded. Nonblank IDs require signed numeric cents within ±1,000,000. The 18-by-four area fits every distinct key from both declared ranges.
Group agreement does not prove row agreement
Two exports can have the same per-ID total and different individual transactions. Use row-level source checks when transaction identity matters. This lesson reports grouped differences without deleting or repairing the original sources.
Read the full step-by-step walkthrough
- Build the complete key population
VSTACK puts the eight A IDs above the ten D IDs. Keeping both sides prevents right-only records from disappearing.
- Normalize and deduplicate nonblank keys
Filter empty slots, uppercase the text and retain the first occurrence of every key. The resulting order follows A, then new keys from D.
- Aggregate source A independently
For each key, SUMPRODUCT combines every matching signed amount in B. A missing key receives numeric zero.
- Aggregate source B independently
Use the same key population and identity policy for D:E. Repeated receipts and credits remain in the total.
- Show both totals and the residual
HSTACK returns key, total A, total B and A minus B. A zero residual does not hide the source totals or prove that individual rows match.
- Handle a completely empty union
When both ID lists contain only empty slots, return No keys in one cell. All other positions in the maximum report area stay blank.
Things to watch
Zero totals disappear
Choose keys from ID presence, not amount tests.
The two totals use different matching policies
Apply the same literal, case-normalized key rule on both sides.
About this example
These original single-anchor tasks use a declared ASCII ID policy, signed numeric cents and bounded source rows. Browser calculations and independent rules are checked separately from native Excel/Sheets recalculation, editing and save/reopen.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.