AI distinct count audit: two lists, three valid totals

What does a shared ID mean in this exercise?

AI means artificial intelligence. An AI distinct count audit should define its counting unit before accepting a combined total. Our fictional example contains two workshop signup lists from the same stipulated registry, at the same cutoff, with the same eligibility rule. An exact nonempty ID has the same meaning in both lists: the same fictional registrant record. That meaning is supplied by the exercise, not inferred from names.

Every supplied ID is eligible. Each list is internally unique: no ID appears twice within list A or twice within list B. However, a registrant can legitimately sign up for both workshops. Shared membership is therefore not an error to repair. The task is to count each different ID appearing in at least one list once, while preserving evidence of both memberships.

Write a contract card: “Practice V1; same registry, cutoff and eligible units; exact text IDs; internally unique lists; retain both source lists; return membership evidence and distinct combined IDs.” Do not silently reinterpret the unit as attendance, completed training, real people reached by advertisements or actual customer demand. None of that evidence is supplied.

The business data analysis guide gives broader advice about record definitions. This exercise ends with a smaller artifact: a saved membership ledger separating nine signup memberships, six distinct IDs and three IDs present in only one list.

Keep both fictional signup lists intact

These two cards were constructed for a manual exercise. They do not report a real workshop, a survey, an AI experiment or a client result. The letters M1 through M6 are arbitrary identifiers, not contact details. The data says who is on each fictional signup list; it does not say who attended or completed anything.

Count list A independently: five different IDs. Count list B independently: four. Adding these counts gives 5+4=9 membership records across the sources. That calculation is valid if the question is how many list memberships were supplied. It does not yet answer how many different registrant IDs appear anywhere in the two lists.

Identify the shared IDs directly: M3, M4 and M5 appear in both. Each contributes two memberships to the nine-record sum. M1 and M2 occur only in A, while M6 occurs only in B. Do not delete the shared source entries; they are the evidence that those registrants signed up for both fictional workshops.

Before continuing, verify internal uniqueness rather than assuming a list heading guarantees it. This fixed card passes that check. A real list containing repeated rows would need an explicitly defined counting rule before the simple source-list counts were used. The spreadsheet prompt examples can help describe that boundary without changing the source file.

Build one row per different ID, with two membership flags

Start a separate ledger. Add M1 through M5 from the first list, with A marked yes. Read the second list one ID at a time. For M3, M4 and M5, mark B yes on the existing rows rather than creating another combined-ID row. Add M6 as a new row with A no and B yes. The ledger retains the original membership facts while counting different IDs once.

Copy three partition lists beneath the table. A only contains M1/M2. Both contains M3/M4/M5. B only contains M6. Their sizes are two, three and one, adding to six. A partition here means non-overlapping groups that cover all IDs in the combined source. Every ledger row belongs in exactly one of these three groups.

The original source lists are not non-overlapping: they share three IDs. This distinction explains why the partition sizes can be added directly but the original list sizes cannot be relabelled as a distinct combined total. Keep both representations so another reader can check where the difference comes from.

Do not add a row marked no in both columns. Such an ID is absent from both supplied sources and is outside this combined list. Missing evidence in a real record should be investigated, not replaced with an invented identifier.

Calculate three totals with three different names

The union means all different IDs found in either list, including IDs found in both. Here it is M1, M2, M3, M4, M5 and M6, so its count is six. The intersection means the IDs common to both lists: M3, M4 and M5, count three. These terms describe membership, not a decision to merge contact records.

Open a calculator and enter 5+4−3. The result is six. Adding five and four counted each shared ID twice. Subtracting the intersection removes one excess count for each shared ID, leaving each in the combined result once. It does not remove shared IDs entirely. Check the answer against the six actual ledger rows, not just the formula.

Now ask a different question: which IDs occur in exactly one source list? M1, M2 and M6 do, giving three. The shared IDs do not satisfy that narrower question, even though they belong in the union. Write the three named controls: nine source memberships; six different IDs in at least one list; three different IDs in exactly one list.

Microsoft’s UNIQUE documentation distinguishes returning distinct values from returning only values that occur exactly once. That conceptual distinction helps explain why six and three are both valid answers to different questions. We are not running the function or claiming that a product has verified the registry’s identity meaning.

For another calculation where category labels answer different questions, see the classification error ledger. Do not borrow its denominators here: this task is a two-list membership count, not a classification-quality score.

Which proposed summaries does the ledger support?

Claim one says: “There are nine distinct registrants across the two signup lists.” Revise it. Nine is the source membership sum, while six is the distinct-ID count. Cite M3, M4 and M5 as the shared evidence. A number can be correctly added yet incorrectly named, so repair the reporting label as well as the final sentence.

Claim two says: “Remove every shared ID and the complete combined list has three registrants.” That output contains only the exactly-one group. It is not a complete union. Keep M3, M4 and M5 once in the distinct combined list and preserve their yes/yes flags. No source record is deleted or overwritten in this exercise.

Claim three says: “Six different IDs proves six people attended the workshops.” Mark it unsupported. Signup is not attendance, and the stipulated ID mapping is not an independent real-world identity check. Likewise, the exercise cannot validate deduplicated reach across advertising platforms, where identifiers, periods and units may differ.

Joy Morris’s authored combinatorics text explains the two-set rule: add the source set sizes and subtract their intersection. The same section partitions a union into A only, both and B only. We use that small counting principle, not its unrelated teaching examples or a claim about marketing measurement.

The contact evidence review investigates uncertain identity matches; our mapping is already stipulated. The table join audit enumerates matching source pairs and copied units. Our ledger instead retains one row per different ID with two membership flags. Shared ideas about preserving source evidence do not make those outputs interchangeable.

What must be in the file before you press Save?

On Windows, open Start, type Notepad and launch the application. Create a document headed “Two-list membership audit V1: fictional workshop cards.” Paste the contract, both source lists, all six ledger rows and the three partition lists. Add the three named totals, then all three claim decisions with the source evidence behind each decision. Preserve the shared IDs in the union.

Press Ctrl+S, choose Documents and save the document as two-list-audit-V1.txt. Close the editor and reopen the file from that folder. Check that M3/M4/M5 still appear on both source lists and once each in the combined ledger. Confirm that nine memberships, six distinct IDs and three exactly-one IDs have separate labels. Verify that no attendance statement has slipped into the acceptance note.

For an optional AI comparison, provide only these fictional cards to an assistant you already use. Copy this request: “Use the supplied exact same-registry IDs. Show one combined ledger row per different ID with A and B membership flags. List A only, both and B only. Calculate source membership sum, distinct union and exactly-one counts separately. Keep shared IDs once in the union without deleting source entries. Do not infer identity, attendance or cross-platform reach. Assess the three claims using source IDs.”

Compare the response with the manual answer key. If the assistant returns only M1/M2/M6 as the complete union, correct the question boundary. If access is unavailable, write “optional AI comparison not performed.” The manual ledger remains a complete result. No contacts are merged, no workshop system is changed and no messages are sent.

A suggested fifteen-minute allowance is three minutes for scope and source, four for membership flags, four for counts and claims, and four for saving and reopening. This is a planning suggestion, not a measured user-completion result. In my earlier writing about perfection and effectiveness, I described project delays from pursuing every detail. Applied here, that working principle means finishing one reconstructable checking file before redesigning a reporting workflow; it is not a personal registry experiment or client performance claim.

Finish with a limited acceptance statement: “Under the supplied registry rule, six distinct IDs appear in at least one signup list. Three of them appear in both. Attendance remains unverified.” Keep that boundary visible when sharing the note, because a polished combined total cannot supply evidence that the original cards never contained.