Accounting · Original fictional case
Find repeat invoices without losing legitimate bills
An invoice import contains exact repeats and a disagreement about one amount. Prepare the accepted invoice count and payable total, with a separate count of unresolved rows.
Related work: Bookkeeping, Accounting, and Auditing Clerks · O*NET task 2486.
Your source data
| supplier | invoice | amount usd |
|---|---|---|
| A | 100 | 1000 |
| A | 100 | 1000 |
| A | 101 | 500 |
| B | 100 | 750 |
| A | 102 | 200 |
| A | 102 | 250 |
| B | 101 | 0 |
The rules
- Use supplier + invoice as the business key. The same invoice number from different suppliers is not a duplicate.
- Collapse identical repeated rows to one. If amounts disagree for a business key, hold every row for that key.
- Keep a genuine zero invoice. Amounts are whole USD for this fictional exercise.
A method you can reuse
- Group the seven input rows by the two-part business key before adding amounts. A/100 is an exact repeat; B/100 is a different bill.
- Move both A/102 rows to an exception note. Choosing either 200 or 250 would hide the disagreement.
- Add the four accepted amounts, then reconcile raw rows = accepted invoices + repeated rows removed + held rows.
A common wrong turn
Summing unique amounts loses valid bills that happen to have the same value. Deduplicate by a declared business key, then inspect conflicting values.
Use it in a handoff
For a real import, agree whether invoice identifiers are case-sensitive and how currency is represented. Add supplier and currency to the key when policy requires it.
Check your calculation
Enter numbers without currency or percent symbols. A matching result confirms this exercise’s calculation; keep the method in your review note.
See the worked answer
Accepted invoices: 4
A/100, A/101, B/100 and B/101 remain.
Accepted total (USD): 2250
1000 + 500 + 750 + 0 = 2250.
Exact repeat rows removed: 1
Only the second identical A/100 row is removed.
Rows held for conflict: 2
Both A/102 rows are held; 4 + 1 + 2 = 7 source rows.
Source and practice material
Occupational context uses the O*NET® 31.0 Database from the U.S. Department of Labor, Employment and Training Administration, under CC BY 4.0. View the occupation.
Skill Workbench selected and adapted the occupational information and added its own fictional cases, calculations, guidance and worksheets. USDOL/ETA has not approved, endorsed or tested these additions. O*NET® is a trademark of USDOL/ETA.