Reconcile Two ID Lists with Duplicate Controls in Gnumeric

Two lists can have the same row count but represent different records. Compare a fictional planned dispatch list with a received list, separating missing IDs, repeated IDs and records present exactly once on each side.

Scope: free Gnumeric desktop and its 1.12-series manual. No add-on, account or connection is required. Examples use English function names and comma separators; use your locale’s formula separators. The new fixture was independently checked, not executed in Gnumeric.

Enter an imperfect pair

Type Planned ID in A1 and Received ID in F1, then enter A2:A6 and F2:F6:

Row	A: Planned ID	F: Received ID
2	P101	P103
3	P102	P101
4	P103	P103
5	P104	P105
6	P105	P106

Use exact letters and digits, without spaces. This exercise requires five nonblank IDs on each side, one capitalization convention and no wildcard characters. Before calculating, inspect all ten input cells; if any are missing, stop and obtain the missing record rather than treating blank-to-blank matches as success. It is not a case-sensitive, wildcard-safe arbitrary-text matcher. See COUNTIF before adapting it to IDs with *, ? or inconsistent casing.

Row positions intentionally differ. A2 versus F2 is not the correct comparison: an ID may be anywhere in the other list.

Check both directions

Enter In planned, In received, Status in B1:D1. Enter these formulas in row 2 and copy B2:D2 down through row 6:

• B2: =COUNTIF($A$2:$A$6,A2)
• C2: =COUNTIF($F$2:$F$6,A2)
• D2: =IF(B2<>1,"REVIEW PLANNED",IF(C2=0,"MISSING",IF(C2=1,"MATCH","REVIEW RECEIVED")))

The search ranges stay fixed as the current ID changes. Use the existing reference lesson if copying is unfamiliar.

Expected D2:D6: MATCH, MISSING, REVIEW RECEIVED, MISSING, MATCH. P102 and P104 have not been received; P103 was received twice.

For the reverse check, enter In received, In planned, Status in G1:I1. Copy these row-2 formulas through row 6:

• G2: =COUNTIF($F$2:$F$6,F2)
• H2: =COUNTIF($A$2:$A$6,F2)
• I2: =IF(G2<>1,"REVIEW RECEIVED",IF(H2=0,"UNEXPECTED",IF(H2=1,"MATCH","REVIEW PLANNED")))

Expected I2:I6: REVIEW RECEIVED, MATCH, REVIEW RECEIVED, MATCH, UNEXPECTED. P106 is unexpected. Both P103 rows are flagged instead of silently keeping the first.

Summarize without hiding exceptions

Put descriptive labels in K1:K5 and these formulas in L1:L5:

Label	Formula	Expected
Planned rows needing review	=COUNTIF(D2:D6,"<>MATCH")	3
Received rows needing review	=COUNTIF(I2:I6,"<>MATCH")	3
Planned IDs missing	=COUNTIF(D2:D6,"MISSING")	2
Unexpected received rows	=COUNTIF(I2:I6,"UNEXPECTED")	1
Received rows carrying repeated IDs	=COUNTIF(G2:G6,">1")	2

The last result means two affected rows, not two distinct duplicated IDs or two extra records. Here one ID repeats and one occurrence is extra. Do not add the two review totals and call the result six distinct problems: the reports overlap. Matching an ID also says nothing about its quantity, date or description.

Apply a documented correction and retest

For this fictional exercise, assume the source owner confirms F4 should be P102 and F6 should be P104. Make those changes. All ten statuses must become MATCH, and L1:L5 must become 0. Independently list the final received set: P103, P101, P102, P105, P104. Each planned ID occurs exactly once, regardless of order.

Now temporarily change A6 from P105 to P104. D5 and D6 must both show REVIEW PLANNED; received P105 becomes UNEXPECTED, and received P104 becomes REVIEW PLANNED. Restore A6 to P105 and verify all statuses return to MATCH. Never delete a real exception just to improve the report.

Save the audit trail

Keep the original inputs alongside the corrected version and record the correction’s authority. Here that authority is fictional, not an actual approval. Use File → Save As to save a new native .gnumeric workbook, selecting the native format. Reopen it and inspect formulas and final IDs yourself.

When extending beyond row 6, extend all fixed search ranges, status formulas and summaries together. Missing IDs, spaces and inconsistent text conventions need resolution before this bounded checker can be trusted. Never automatically replace an ID merely because a similar one exists. If sorting, keep each complete table together with its diagnostics; see sort/filter technique.

Sources and verification scope

Reviewed 21 October 2026 against the official manual’s COUNTIF, IF, text/formula entry and saving sections. IDs, status expectations and tests are original and independently checked by counts and sets. No native execution, save/reopen or screenshot was performed for this tutorial.