1
00:00:00,080 --> 00:00:04,232
Two ID lists can contain the same item on different rows.

2
00:00:04,232 --> 00:00:08,383
A simple cell-by-cell comparison would mislabel those as missing.

3
00:00:08,383 --> 00:00:11,780
This fictional ticket exercise checks both directions with COUNTIF:

4
00:00:11,780 --> 00:00:15,176
which old IDs disappeared, and which new IDs arrived.

5
00:00:15,176 --> 00:00:20,083
We should end with exactly two old-only IDs, two new-only IDs,

6
00:00:20,083 --> 00:00:22,725
and three IDs present in both lists.

7
00:00:23,325 --> 00:00:26,729
Import the fictional CSV into a private Google Sheet.

8
00:00:26,729 --> 00:00:30,889
Column A is the old list; column B is the new

9
00:00:30,889 --> 00:00:32,781
list in a different order.

10
00:00:32,781 --> 00:00:38,076
A simple A2=B2 comparison would flag PR-101 and PR-104 as different

11
00:00:38,076 --> 00:00:41,102
even though both appear somewhere in each list.

12
00:00:41,102 --> 00:00:46,397
We need membership anywhere in the opposite column, not equality on the same row.

13
00:00:46,997 --> 00:00:49,425
In C1 type Old-list status.

14
00:00:49,425 --> 00:00:55,495
In C2 enter =IF(COUNTIF($B$2:$B$6,A2)=0,"Missing from new","Present")

15
00:00:55,495 --> 00:00:57,114
and fill through C6.

16
00:00:57,114 --> 00:01:02,375
The dollar signs keep the five-cell new-list range fixed as the

17
00:01:02,375 --> 00:01:05,207
old-list reference changes row by row.

18
00:01:05,207 --> 00:01:09,659
Google documents COUNTIF as a conditional count and IF as the

19
00:01:09,659 --> 00:01:11,682
branch that labels zero matches.

20
00:01:12,282 --> 00:01:14,972
In D1 type New-list status.

21
00:01:14,972 --> 00:01:23,041
In D2 enter =IF(COUNTIF($A$2:$A$6,B2)=0,"New only","Present") and fill through D6.

22
00:01:23,041 --> 00:01:28,421
This reverse pass catches arrivals that a one-way check cannot show.

23
00:01:28,421 --> 00:01:34,249
PR-107 and PR-106 are new only, regardless of their row numbers.

24
00:01:34,849 --> 00:01:39,142
Read PR-101, PR-103, and PR-104 in both columns.

25
00:01:39,142 --> 00:01:43,435
Their status cells should say Present even though the IDs do

26
00:01:43,435 --> 00:01:45,776
not line up row for row.

27
00:01:45,776 --> 00:01:50,069
If one is unexpectedly missing, inspect for a pasted space or

28
00:01:50,069 --> 00:01:52,801
an edited ID before changing the formula.

29
00:01:52,801 --> 00:01:56,704
Do not silently delete an item to force a match.

30
00:01:57,304 --> 00:02:01,677
Filter the status columns for Missing from new and New only,

31
00:02:01,677 --> 00:02:05,255
or copy those four IDs into a review note.

32
00:02:05,255 --> 00:02:11,617
The old-only IDs may represent omissions; the new-only IDs may represent legitimate new records.

33
00:02:11,617 --> 00:02:15,195
The formula identifies differences, not which list is correct.

34
00:02:15,195 --> 00:02:19,171
Investigate each exception against the source system before merging anything.

35
00:02:19,771 --> 00:02:23,796
For a larger real list, update both COUNTIF ranges to cover

36
00:02:23,796 --> 00:02:26,723
all filled IDs before relying on the statuses.

37
00:02:26,723 --> 00:02:31,114
The fixed ranges in this five-row exercise stop at row six;

38
00:02:31,114 --> 00:02:35,871
adding a sixth new ID without expanding them would make the check incomplete.

39
00:02:35,871 --> 00:02:41,360
Recount the input rows and spot-check a known shared ID after every range edit.

40
00:02:41,960 --> 00:02:46,522
The old-only IDs are PR-102 and PR-105.

41
00:02:46,522 --> 00:02:51,085
The new-only IDs are PR-107 and PR-106.

42
00:02:51,085 --> 00:02:58,385
PR-101, PR-103, and PR-104 are shared even though they sit on different rows.

43
00:02:58,385 --> 00:03:03,860
The formulas identify exceptions; they do not decide which system is right.

44
00:03:03,860 --> 00:03:06,598
Inspect those four records before merging.

45
00:03:06,598 --> 00:03:12,073
The guide provides the source lists, both formulas, and the answer check.