Match the orders
Map three order layouts to one set of fields and standardize dates, IDs and amounts.
01 / FIVE-FILE DEMOSALES, PAYMENTS & RETURNS
FIVE FILES.
ONE VIEW.
Web, marketplace and point of sale orders arrive with separate payment and return files. We join them, reconcile the amounts and give the unresolved records their own list.
5CSV FILES
34SOURCE ROWS
10MATCHED ORDERS
3ORDERS TO REVIEW
02 / INPUTSFIVE DIFFERENT LAYOUTS
THE FILES
WE START WITH.
Order IDs have different casing and spacing. Dates and money formats vary. One order has two totals; a payment appears twice; a return points to an order missing from the exports.
Web orders
web_orders.csv / 7 rows| Order # | Placed at | Total | CCY | State |
|---|---|---|---|---|
| #w-101 | 09/03/2026 | $120.00 | usd | Paid |
| W-102 | 09/04/2026 | $80.00 | USD | Paid |
| W-103 | 09/04/2026 | $50.00 | usd | Paid |
| W-103 | 09/04/2026 | $50.00 | usd | Paid |
| W-104 | 09/05/2026 | $70.00 | USD | Paid |
| W-105 | 09/05/2026 | $39.99 | USD | Paid |
| W-106 | 09/06/2026 | $25.00 | USD | Paid |
Marketplace orders
marketplace_orders.csv / 5 rows| ref | day | gross | currency | state |
|---|---|---|---|---|
| M-201 | 03 Sep 2026 | 40.00 | USD | completed |
| m-202 | 04 Sep 2026 | $75.00 | usd | completed |
| M-203 | 04 Sep 2026 | CA$60.00 | CAD | completed |
| M-204 | 05 Sep 2026 | 30.00 | USD | completed |
| M-204 | 05 Sep 2026 | 35.00 | USD | completed |
Point of sale orders
pos_orders.csv / 3 rows| ticket | sale_date | paid_total | currency |
|---|---|---|---|
| P-301 | 2026.09.04 | 18.50 | USD |
| P-302 | 2026.09.05 | 22.00 | USD |
| P-303 | 2026.09.06 | 15.00 | USD |
Payment activity
payments.csv / 16 rows| transaction | order_ref | date | charged | currency |
|---|---|---|---|---|
| PAY-001 | w-101 | 2026-09-03 | 120.00 | USD |
| PAY-002 | W-102 | 2026-09-04 | 50.00 | USD |
| PAY-003 | W-102 | 2026-09-04 | 30.00 | USD |
| PAY-004 | W-103 | 2026-09-04 | 50.00 | USD |
| PAY-005 | W-104 | 2026-09-05 | 70.00 | USD |
| PAY-006 | W-105 | 2026-09-05 | 39.99 | USD |
| PAY-007 | W-106 | 2026-09-06 | 25.00 | USD |
| PAY-008 | M-201 | 2026-09-03 | 40.00 | USD |
| PAY-009 | M-202 | 2026-09-04 | 75.00 | USD |
| PAY-010 | M-203 | 2026-09-04 | 60.00 | CAD |
| PAY-011 | M-204 | 2026-09-05 | 30.00 | USD |
| PAY-012 | P-301 | 2026-09-04 | 18.50 | USD |
| PAY-013 | P-302 | 2026-09-05 | 20.00 | USD |
| PAY-014 | X-999 | 2026-09-06 | 12.00 | USD |
| PAY-008 | M-201 | 2026-09-03 | 40.00 | USD |
| PAY-016 | P-303 | 2026-09-06 | 18,O0 | USD |
Returns
returns.csv / 3 rows| return_ref | order_ref | refund | currency |
|---|---|---|---|
| R-01 | W-103 | $20.00 | USD |
| R-02 | m-202 | 10.00 | USD |
| R-03 | X-999 | 5.00 | USD |
34 ROWS ACROSS FIVE FILES · SOURCE ROWS STAY TRACEABLE BY FILE AND ROW NUMBER
03 / WORKHOW THE FILES COME TOGETHER
ONE ORDER
VIEW.
We agree on the order key and source formats, then apply the same rules across the files.
Join the money
Attach charges and refunds to each order. Two payments for W-102 add to its $80 total.
Separate the problems
Set exact duplicates aside and hold conflicting totals, unmatched activity and invalid amounts for review.
04 / OUTPUTRECONCILED ORDERS
THE FILE
YOU CAN USE.
Ten orders have matching charges. Refunds sit beside their orders. Each row carries its source filename.
| Order ID | Date | Source | Order total | Currency | Charges | Refunds | Charges less refunds |
|---|---|---|---|---|---|---|---|
| M-201 | 2026-09-03 | marketplace_orders.csv | 40.00 | USD | 40.00 | 0.00 | 40.00 |
| M-202 | 2026-09-04 | marketplace_orders.csv | 75.00 | USD | 75.00 | 10.00 | 65.00 |
| M-203 | 2026-09-04 | marketplace_orders.csv | 60.00 | CAD | 60.00 | 0.00 | 60.00 |
| P-301 | 2026-09-04 | pos_orders.csv | 18.50 | USD | 18.50 | 0.00 | 18.50 |
| W-101 | 2026-09-03 | web_orders.csv | 120.00 | USD | 120.00 | 0.00 | 120.00 |
| W-102 | 2026-09-04 | web_orders.csv | 80.00 | USD | 80.00 | 0.00 | 80.00 |
| W-103 | 2026-09-04 | web_orders.csv | 50.00 | USD | 50.00 | 20.00 | 30.00 |
| W-104 | 2026-09-05 | web_orders.csv | 70.00 | USD | 70.00 | 0.00 | 70.00 |
| W-105 | 2026-09-05 | web_orders.csv | 39.99 | USD | 39.99 | 0.00 | 39.99 |
| W-106 | 2026-09-06 | web_orders.csv | 25.00 | USD | 25.00 | 0.00 | 25.00 |
13 DISTINCT ORDERS = 10 MATCHED + 3 HELD FOR REVIEW
05 / REVIEWEXACT SOURCE LOCATIONS
WHAT NEEDS
A DECISION.
Six items need a closer look. The three affected orders stay out of the reconciled file until their amounts are clear.
| File | Row | Order ID | What needs checking |
|---|---|---|---|
| payments.csv | 15 | X-999 | payment has no matching order |
| payments.csv | 17 | P-303 | payment amount 18,O0 needs checking |
| returns.csv | 4 | X-999 | refund has no matching order |
| marketplace_orders.csv | 5, 6 | M-204 | two order totals; confirm the correct one |
| pos_orders.csv | 3 | P-302 | order and payment totals differ |
| pos_orders.csv | 4 | P-303 | payment needs checking |
TWO EXACT DUPLICATE ROWS SET ASIDE: payments.csv row 16 repeats row 9; web_orders.csv row 5 repeats row 4.
06 / TOTALSMATCHED ORDERS ONLY
TOTALS BY
CURRENCY.
Charges and refunds from the ten matched orders add up separately by currency.
07 / SIZE CHECKLOCAL PROCESSING TEST
BUILT FOR
BIG FILES.
We ran the same kind of file join on five generated CSVs with 1,000,000 source rows. The test matched 450,000 orders, separated 50,000 repeated payment rows and attached 50,000 refunds.
1,000,000ROWS ACROSS FIVE CSV FILES
Local test result: 450,000 matched orders, with counts and amounts checked after the join.
08 / YOUR FILESONE CLEAR SCOPE
GOT A DATA
PILE?
Tell us how many files you have and what the finished dataset should look like. We’ll confirm the rules, price and delivery date.
REQUEST A QUOTE ↗