Final project · Spreadsheet

Handoff a clean order file

Project dataset

My saved work →

Project dataset Fresh dataset challenge →Each dataset keeps its own attempts.

Your task

Return order_id, date, region, name, amount in that order, sorted by numeric order_id. Amounts are integer whole dollars.
For each order_id, collapse byte-for-byte identical records to one. If any source field disagrees within an ID, exclude every record for that ID; never choose a maximum amount.
Accept only real calendar dates written YYYY/MM/DD. Exclude ambiguous or impossible dates. Convert accepted dates to YYYY-MM-DD.
An empty region becomes Unknown. Trim outer spaces and collapse repeated spaces in names. Preserve numeric zero amounts.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

order_idnumber
datestring
regionstring
namestring
amountnumber

Expected output: 59 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Empty CSV cells stay empty; the letters NULL are text. Apply the task’s declared replacement rules. Formulas are evaluated before checking.

Reconciliation checks

68 raw records must reconcile to 59 accepted unique orders, five exact repeat records, two records under one conflicting key, and two invalid-date records.
Accepted amount checksum: 6161 whole dollars.
Inspect the conflicting key before deduplication; first-row-wins would incorrectly publish it.

Need a next step?

Hint 1: where to start

Run each cleaning rule once, then inspect the remaining rows.

Hint 2: build the method

Write down the eligible keys before aggregating. Inspect each anomaly against the output contract.

Hint 3: check your work

Use the reconciliation counts after constructing the complete output; a matching total alone does not prove the rows are correct.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

68 original source records.

Data dictionary

order_id integer: Business key; repeated imports can be exact duplicates or conflicting records.
date text: Raw date; only real YYYY/MM/DD dates are accepted.
region text: Reporting region; an empty cell has the declared Unknown default.
name text: Customer label; normalize spaces only.
amount integer: Whole dollars; zero is valid and must remain numeric.

View source data

order_id,date,region,name,amount
1000,2026/09/01,,  Client  1  ,0
1001,2026/09/02,West,  Client  2  ,28
1002,2026/09/03,North,  Client  3  ,31
1003,2026/09/04,East,  Client  4  ,34
1004,2026/09/05,West,  Client  5  ,37
1005,2026/09/06,North,  Client  6  ,40
1006,2026/09/07,East,  Client  7  ,43
1007,2026/09/08,West,  Client  8  ,46
1008,2026/09/09,North,  Client  9  ,49
1009,2026/09/10,,  Client  10  ,52
1010,2026/09/11,West,  Client  11  ,0
1011,2026/09/12,North,  Client  12  ,58
1012,2026/09/13,East,  Client  13  ,61
1013,2026/09/14,West,  Client  14  ,64
1014,2026/09/15,North,  Client  15  ,67
1015,2026/09/16,East,  Client  16  ,70
1016,2026/09/17,West,  Client  17  ,73
1017,2026/09/18,North,  Client  18  ,76
1018,2026/09/19,,  Client  19  ,79
1019,2026/09/20,West,  Client  20  ,82
1020,2026/09/21,North,  Client  21  ,0
1021,2026/09/22,East,  Client  22  ,88
1022,2026/09/23,West,  Client  23  ,91
1023,2026/09/24,North,  Client  24  ,94
1024,2026/09/25,East,  Client  25  ,97
1025,2026/09/26,West,  Client  26  ,100
1026,2026/09/27,North,  Client  27  ,103
1027,2026/09/28,,  Client  28  ,106
1028,2026/09/01,West,  Client  29  ,109
1029,2026/09/02,North,  Client  30  ,112
1030,2026/09/03,East,  Client  31  ,0
1031,2026/09/04,West,  Client  32  ,118
1032,2026/09/05,North,  Client  33  ,121
1033,2026/09/06,East,  Client  34  ,124
1034,2026/09/07,West,  Client  35  ,127
1035,2026/09/08,North,  Client  36  ,130
1036,2026/09/09,,  Client  37  ,133
1037,2026/09/10,West,  Client  38  ,136
1038,2026/09/11,North,  Client  39  ,139
1039,2026/09/12,East,  Client  40  ,142
1040,2026/09/13,West,  Client  41  ,0
1041,2026/09/14,North,  Client  42  ,148
1042,2026/09/15,East,  Client  43  ,151
1043,2026/09/16,West,  Client  44  ,154
1044,2026/09/17,North,  Client  45  ,157
1045,2026/09/18,,  Client  46  ,160
1046,2026/09/19,West,  Client  47  ,163
1047,2026/09/20,North,  Client  48  ,166
1048,2026/09/21,East,  Client  49  ,169
1049,2026/09/22,West,  Client  50  ,172
1050,2026/09/23,North,  Client  51  ,0
1051,2026/09/24,East,  Client  52  ,178
1052,2026/09/25,West,  Client  53  ,181
1053,2026/09/26,North,  Client  54  ,184
1054,2026/09/27,,  Client  55  ,187
1055,2026/09/28,West,  Client  56  ,190
1056,2026/09/01,North,  Client  57  ,193
1057,2026/09/02,East,  Client  58  ,196
1058,2026/09/03,West,  Client  59  ,199
1059,2026/09/04,North,  Client  60  ,202
1000,2026/09/01,,  Client  1  ,0
1001,2026/09/02,West,  Client  2  ,28
1002,2026/09/03,North,  Client  3  ,31
1003,2026/09/04,East,  Client  4  ,34
1004,2026/09/05,West,  Client  5  ,37
1008,2026/09/09,North,  Client  9  ,999
1060,03/04/2026,East,Ambiguous Date,100
1061,2026/09/31,West,Calendar Error,120

Your work

Active 0:00 · Paused 0s

Edit the grid or the CSV. Formulas use grid row numbers, including the header row.

Business key

Browser formula support

This practice grid supports SUM, SUMIF, COUNTIF, IF, TRIM and an exact-match LOOKUP, plus arithmetic, comparisons, cell references and single-column ranges. For example: =SUM(B2:B3).

LOOKUP here returns the first exact match, or an empty value if none exists. Native Excel LOOKUP uses different matching rules. Excel workbook features, PivotTables and its full function library require Excel.

Keep missing values explicit. Arithmetic can convert an empty reference to zero; use a replacement only when the task permits it. Circular references are rejected.

CSV answer

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the reference answer

order_id,date,region,name,amount
1000,2026-09-01,Unknown,Client 1,0
1001,2026-09-02,West,Client 2,28
1002,2026-09-03,North,Client 3,31
1003,2026-09-04,East,Client 4,34
1004,2026-09-05,West,Client 5,37
1005,2026-09-06,North,Client 6,40
1006,2026-09-07,East,Client 7,43
1007,2026-09-08,West,Client 8,46
1009,2026-09-10,Unknown,Client 10,52
1010,2026-09-11,West,Client 11,0
1011,2026-09-12,North,Client 12,58
1012,2026-09-13,East,Client 13,61
1013,2026-09-14,West,Client 14,64
1014,2026-09-15,North,Client 15,67
1015,2026-09-16,East,Client 16,70
1016,2026-09-17,West,Client 17,73
1017,2026-09-18,North,Client 18,76
1018,2026-09-19,Unknown,Client 19,79
1019,2026-09-20,West,Client 20,82
1020,2026-09-21,North,Client 21,0
1021,2026-09-22,East,Client 22,88
1022,2026-09-23,West,Client 23,91
1023,2026-09-24,North,Client 24,94
1024,2026-09-25,East,Client 25,97
1025,2026-09-26,West,Client 26,100
1026,2026-09-27,North,Client 27,103
1027,2026-09-28,Unknown,Client 28,106
1028,2026-09-01,West,Client 29,109
1029,2026-09-02,North,Client 30,112
1030,2026-09-03,East,Client 31,0
1031,2026-09-04,West,Client 32,118
1032,2026-09-05,North,Client 33,121
1033,2026-09-06,East,Client 34,124
1034,2026-09-07,West,Client 35,127
1035,2026-09-08,North,Client 36,130
1036,2026-09-09,Unknown,Client 37,133
1037,2026-09-10,West,Client 38,136
1038,2026-09-11,North,Client 39,139
1039,2026-09-12,East,Client 40,142
1040,2026-09-13,West,Client 41,0
1041,2026-09-14,North,Client 42,148
1042,2026-09-15,East,Client 43,151
1043,2026-09-16,West,Client 44,154
1044,2026-09-17,North,Client 45,157
1045,2026-09-18,Unknown,Client 46,160
1046,2026-09-19,West,Client 47,163
1047,2026-09-20,North,Client 48,166
1048,2026-09-21,East,Client 49,169
1049,2026-09-22,West,Client 50,172
1050,2026-09-23,North,Client 51,0
1051,2026-09-24,East,Client 52,178
1052,2026-09-25,West,Client 53,181
1053,2026-09-26,North,Client 54,184
1054,2026-09-27,Unknown,Client 55,187
1055,2026-09-28,West,Client 56,190
1056,2026-09-01,North,Client 57,193
1057,2026-09-02,East,Client 58,196
1058,2026-09-03,West,Client 59,199
1059,2026-09-04,North,Client 60,202

Check conflicts before dropping duplicate IDs, then validate the calendar date. Blank region has an explicit default; zero money does not mean missing money.