Merging two lists without double counting: five matching rules

You have a list of event sign-ups from a form and another from a spreadsheet someone kept by hand. Together they should come to about three hundred people. Merged carelessly they come to four hundred and twenty, because the same people appear twice in slightly different clothes: “J. Smith” and “Jane Smith”, two email addresses, a stray space.

Merging lists is a classic handoff, and the mistakes in it are quiet: a total that is a bit too high, a person who receives two invitations. A few matching rules, agreed before the merge, make the result checkable. They work whoever does the merging, a colleague, a script or an AI assistant. The examples below are illustrative.

Rule 1: Pick one key, and say what it is

Decide which field identifies an item: an email address, an order number, a membership ID. Names make poor keys because people write them in different ways. If no single field will do, say which two must both match.

Rule 2: Normalize before you compare

Trim spaces, make capitalization the same, strip stray punctuation and write dates and phone numbers in one format. Most duplicates that a merge misses are the same value written two ways. Do this on copies, and leave the originals untouched. Do not normalize too far: removing every space from a name or every dot from an email can make two different people look the same. Normalize the key and leave the display values as they were.

Rule 3: Decide what wins when two rows disagree

When the same person appears with two different phone numbers, which one is kept? The newest, the one from the form, the one with more filled fields? Write the rule down. A merge without one picks winners arbitrarily, and nobody can explain the results later.

Rule 4: Never merge near matches silently

Exact matches on the key can merge automatically. Near matches, such as “Jon Smith” at the same company with a different email, go on a review list for a person to decide. Agree a rough threshold for what counts as near: the same surname and company, say, or the same phone number under a different name. A wrong merge hides a person. A missed merge only costs a duplicate.

Rule 5: Keep an audit column

Add a column that says where each row came from, “form”, “sheet” or “both”, and a note when a rule chose between two values. It costs a few seconds per run and it is the only way to answer “why is this person on the list twice?” a month from now.

Check the count at the end

Write down the row count of each list before you start. The merged total should equal the rows in the first list plus the rows in the second, minus the rows that were merged. If the arithmetic fails, something was dropped or merged twice, and it is better to find that out before anyone sends an invitation.

A hypothetical merge

List A has 180 rows and list B has 240. Sixty match exactly on email after normalizing. Nine more are near matches and go on the review list, where you merge six and keep three apart. The expected total is 180 plus 240 minus 66, which is 354. The audit column shows 114 from A only, 174 from B only and 66 from both, and 114 plus 174 plus 66 is 354. The numbers agree, so the merge is ready to use.

Where Octuo fits

We’re the Octuo team. Octuo is a personal assistant and one starting point for work across AI, tools and specialist services. It hands tasks to supported specialist tools on your computer once you’ve installed, set up and authorized them, and to outside services, and it reports back when a job is done. It is a signed and notarized download, it needs an internet connection, and it uses cloud-hosted models for most calls. Octuo is available for macOS.