How to Dedupe Lists From Different Sources When the Columns Don't Match

Why columns never match across sources

Each system names fields for its own world. A shop cares about orders, so it says “Buyer Email.” A form tool says “Email Address.” A clerk's sheet says whatever the clerk typed at the top. Field order differs. Some lists split names and addresses into many fields. Others cram them into one. None of this is wrong. It just means the lists cannot be compared until someone translates.

The shared names in the right column are yours to choose — short, plain, one per fact. That set of names is called a schema: the shape every list will take before dedupe. Pick it once, write it down, and use it for each new source. The second list you map will take half the time of the first, and the third less still.

Choose the shared names with the end in mind. Your CRM, your mail tool, and your own eyes all have to read them. Short names in plain words — email, phone, city — beat clever short codes that no one can decode in six months. A schema is a small language your team agrees to speak about the data. Like any language, it works when it is plain, shared, and slow to change.

Step 1: build the column map

For each source, write three columns on paper or in a note: source field, shared field, and any fix the values need. “Phone → phone, digits only.” “Full Name → first_name + last_name, split at the last space.” Work through every field. Fields with no shared home get a call: drop them, park them in notes, or add a shared field for them.

Split and joined fields need the most care. Splitting “Full Name” into first and last sounds easy until you meet “Mary Ann Smith,” “Smith, Mary Ann,” and “Dr. J. R. Smith.” Use a set rule — split at the first space for “First Last,” at the comma for “Last, First” — and flag the odd ones for a hand fix. Joining is safer: first and last into one full-name field rarely goes wrong. When you can choose, map toward the joined form for matching, and keep the split forms too if your CRM wants them.

Step 2: convert the values to one form

Same column names are not enough. The values inside must take one form too. Dates are the classic trap: one source writes 03/04/2026 to mean March 4, another to mean April 3. Pick the meaning per source from what you know about it, convert all dates to one written form, and spot-check a few rows where the day is over 12 — those prove which way the source meant it.

Do the same for phones (one format, or digits only), states and countries (codes or full names, not both), and firm suffixes. This is the normalization step from single-list cleaning, applied per source. It feels slow. It is also where most cross-source duplicates are won or lost: a pair cannot match on values that were never made comparable in the first place.

If one source is much dirtier than the rest — hand-typed, say — clean it on its own first with the steps in cleaning a CSV before import, then map it. Dirty values mapped into a clean schema just spread the dirt.

One value family needs a joint call across all sources at once: units and money. One list prices in dollars, one in cents. One counts “seats,” one counts “members.” If those fields matter to your match or your merges, set the shared unit in the map and convert at map time. A factor of one hundred, missed, will fake clashes all through review — and a few will slip past tired eyes.

Step 3: stack, tag the source, and dedupe

Now stack the mapped lists under the shared schema. Add a “source” column to each list first, filled with the source name and date. Then dedupe the stacked file as one list: exact groups on email, fuzzy groups on name plus phone or address, review of each group. The source tag earns its keep in review — when two rows clash, the row from the source you trust more, or the newer source, usually wins.

This is exactly the flow DedupeCSV is shaped for: upload, pick the key columns on the shared schema, review the clusters, and download the clean list with its audit file. Lists that start life in one system can also be compared two at a time with the reconcile view on paid plans, which shows what is added, missing, or changed between a new list and your master list — try it on the tool page with your toughest pair of sources.

One stacking trap: do not stack lists with different meanings for the same shared field. If one source's “date” means sign-up date and another's means last order date, they are not the same field, and the map must keep them apart — date_signup and date_last_order. A shared name for two meanings is worse than two names, because the dedupe will trust the clash as a fact.

When a column exists in one source only

Some facts live in just one list: the fair list has a “booth” field, the shop list has order counts. Keep those columns in the schema, blank for the other sources. They cannot help matching — a blank proves nothing — but they must survive the merge. When a group merges, the survivor should absorb these one-source facts like any other gap-filling. A booth note or an order count lost in a merge is data you paid to collect and then threw away.

The flip side: a key column that exists in one source only is a weak key. If only the shop list has emails, email matching will only catch pairs inside that list and pairs where the other list happens to have an email field you mapped from somewhere else. Plan for it: for cross-source pairs, lean on name plus phone or name plus address, and expect a bigger review queue. That is normal for this job. Budget the time.

When the same fact has two homes

Maps get hard when one fact lives in two places. A shop list keeps the phone in “Phone” and also in a notes field, from years of habit. A form list puts the firm name in the name field for half its rows. The map must pick one home per fact per source — and note the stray one, so review can catch the rows where the fact hid in the wrong field.

The tell for a stray fact is a value in the wrong shape. A “name” that holds digits is a phone in the wrong home. An “email” with no @ sign is a note, not an email. In review, scan the stacked file for shape breaks in each column. A quick sort of one column at a time brings the strays to the top or the foot, where your eyes can catch them in bulk.

Some sources send one row per order, not one row per person. Stacked raw, that list drowns the rest: one keen client with forty orders counts forty times. Fold such a list to one row per person first — keep the first and last order dates and the order count as fields — and then map it. Row shape is part of the map. A list of events is not a list of people until you fold it.

The same trap runs in the other way. One row in a source may hold two people: “Mr. and Mrs. Lee,” one email, one phone. Split such rows by hand when you meet them in review, or keep them as one household row if that is how you mail them. What you must not do is let a pair like that merge with a solo row for one of the two. The group would hold three half-facts and serve none of them well.

Codes and IDs need a call per source. Two sources may each have an “ID” column, but the IDs come from two systems and mean two things. Do not map them to one shared ID field. Name them by source — shop_id, fair_id — and match on facts, not codes. A shared name for two code systems will fake matches that no review can unpick at scale.

None of these cases is rare. Each one shows up in most real merges, at least once. The map is where you meet them on paper, at low cost. The dedupe run is where you would meet them in your data, at high cost. That is the whole case for mapping first: paper is the cheapest place to find a flaw.

Keep the map — you will need it again

Sources repeat. The shop will export again next month in the same shape. The fair will send the same sheet next year. If you keep the map — and the settings it led to — the next run is an hour's work, not a day's. In DedupeCSV, that kept routine is a recipe: key columns, form fixes, and merge rules saved and re-run in one click. Keep the paper map too, in the folder with the source files and the audit file.

End as always, with the count check: rows from all sources must equal clean rows plus audited removals. Then spot-check known people: one from each source, one who was in two sources, one with a split name. If those four look right, the map held. If one looks wrong, the map — not the matcher — is the first place to look. A field mapped one column off will bend every row it touches.

Translate first, then compare

Lists from different sources are not dirty. They are foreign to each other. A column map translates them into one shape, value fixes make them comparable, and then ordinary dedupe — the reviewed, gap-filling kind — does the rest. Skip the translation and no matcher, however smart, can see the pairs that cross the seam.

And when the names themselves are the moving part, pair this guide with deduping when names are spelled differently — map the columns first, then let name craft do its work.

Start your map with the next list that lands, not with a grand project. One source mapped well teaches you the shape of the work — and the second source will take half the time.

Sources and further reading

• Preparing your data for eCommons — Cornell University Library — Cornell on preparing CSVs and spreadsheets: clear headers, consistent fields, and documentation that explains each file.
• Work with sensitive data — Stanford University Libraries — Stanford on the direct identifiers in a list — names, emails, phones, addresses — and handling them with care when lists are combined.