1
00:00:00,080 --> 00:00:04,199
The fictional order export contains eight rows worth nine hundred eighty

2
00:00:04,199 --> 00:00:08,692
dollars, but three rows are older updates of an existing order ID.

3
00:00:08,692 --> 00:00:13,185
Our checkable goal is five latest records totaling five hundred sixty dollars.

4
00:00:13,185 --> 00:00:18,053
We will build that result in new tabs, leaving the imported source untouched.

5
00:00:18,653 --> 00:00:22,498
Order O five oh one rises from one hundred twenty to

6
00:00:22,498 --> 00:00:26,343
one hundred twenty five dollars, while O five oh three and

7
00:00:26,343 --> 00:00:28,441
O five oh four also change.

8
00:00:28,441 --> 00:00:30,538
Repeated IDs are not interchangeable rows.

9
00:00:30,538 --> 00:00:34,383
For this exercise, the most recent ISO date in the Updated

10
00:00:34,383 --> 00:00:38,578
column is the winner for each ID; there are no tied dates.

11
00:00:39,178 --> 00:00:43,550
Set this practice Sheet's locale to United States under File, Settings

12
00:00:43,550 --> 00:00:45,371
before using comma-separated formulas.

13
00:00:45,371 --> 00:00:48,286
This does not change your Google Account language.

14
00:00:48,286 --> 00:00:53,386
Import the CSV as Raw, add a Sorted tab, and copy its four headers.

15
00:00:53,386 --> 00:00:57,394
In Sorted A two, enter equals SORT open parenthesis Raw exclamation

16
00:00:57,394 --> 00:01:01,401
A two colon D nine, comma two, comma FALSE close parenthesis.

17
00:01:01,401 --> 00:01:04,316
Whole rows then sort by Updated, newest first.

18
00:01:04,916 --> 00:01:07,131
In Sorted E one, type Decision.

19
00:01:07,131 --> 00:01:11,192
In E two enter equals IF open parenthesis COUNTIF open parenthesis

20
00:01:11,192 --> 00:01:15,253
dollar A dollar two colon A two, comma A two close

21
00:01:15,253 --> 00:01:20,422
parenthesis equals one, comma quote KEEP quote, comma quote OLDER COPY quote close parenthesis.

22
00:01:20,422 --> 00:01:23,006
Fill that formula down through E nine.

23
00:01:23,006 --> 00:01:28,175
Because the rows are newest first, the first ID occurrence is the latest one.

24
00:01:28,175 --> 00:01:29,651
Expect five KEEP flags.

25
00:01:30,251 --> 00:01:34,559
Google Sheets also has Remove duplicates, but this sample's rows with

26
00:01:34,559 --> 00:01:37,431
the same ID have different dates and amounts.

27
00:01:37,431 --> 00:01:41,380
Comparing every column would keep both versions, while deleting by ID

28
00:01:41,380 --> 00:01:45,329
before you decide which row wins can discard the newer record.

29
00:01:45,329 --> 00:01:49,278
Keep the source tab unchanged and check your five flags before

30
00:01:49,278 --> 00:01:50,714
making a clean output.

31
00:01:51,314 --> 00:01:56,602
Create a third tab named Latest and copy the four headers into row one.

32
00:01:56,602 --> 00:02:00,757
In A two enter equals FILTER open parenthesis Sorted exclamation A

33
00:02:00,757 --> 00:02:04,912
two colon D nine, comma Sorted exclamation E two colon E

34
00:02:04,912 --> 00:02:07,556
nine equals quote KEEP quote close parenthesis.

35
00:02:07,556 --> 00:02:11,711
Google documents FILTER as returning only rows that meet the condition.

36
00:02:11,711 --> 00:02:15,111
The expected five rows should spill below the headers.

37
00:02:15,711 --> 00:02:19,669
The expected newest-first IDs are O five oh four, O five

38
00:02:19,669 --> 00:02:23,298
oh three, O five oh one, O five oh five, and

39
00:02:23,298 --> 00:02:24,617
O five oh two.

40
00:02:24,617 --> 00:02:28,246
Their amounts are two hundred five, ninety five, one hundred twenty

41
00:02:28,246 --> 00:02:30,225
five, sixty, and seventy five dollars.

42
00:02:30,225 --> 00:02:32,205
They sum to five hundred sixty.

43
00:02:32,205 --> 00:02:35,833
Compare the downloaded answer file, then check each date and amount

44
00:02:35,833 --> 00:02:37,153
in your own Sheet.

45
00:02:37,753 --> 00:02:42,004
This exercise assumes each order ID has one clear latest date

46
00:02:42,004 --> 00:02:44,324
and that the dates sort correctly.

47
00:02:44,324 --> 00:02:48,575
If two updates share the same timestamp, add an explicit tie

48
00:02:48,575 --> 00:02:50,508
breaker before deciding the winner.

49
00:02:50,508 --> 00:02:54,760
If a real export contains blank IDs, inconsistent date formats, or

50
00:02:54,760 --> 00:02:56,693
changing currencies, clean those first.

51
00:02:56,693 --> 00:03:02,104
The written guide and CSV let you repeat the check without deleting original records.