In this guide 6 sections
Clean duplicates before the import, not after. Match on tax id first, then phone, then a tidied name and address. Merge branches under one head office account and carry each opening balance to the surviving record. Prove it with a sample import.
Export everything and make a working copy
Export customers and vendors from the old billing package or spreadsheet to CSV, keep the original file untouched and do all the cleaning on a copy. Never edit the source.
Add a column called source row. Fill it with the original line number so that, when a merge goes wrong three weeks later, you can trace the party back to where it began.
Use separate sheets. Some businesses buy from and sell to the same firm, and you must decide early, before any merging starts, whether that is one party or two.
Match on the fields that rarely lie
Rank the matching fields by trust. Tax registration number comes first, then mobile or landline phone, then email, then name and address. Names are weakest. People type them in many ways.
Tidy before comparing. Strip full stops, double spaces and words such as Ltd, Pvt, Co and Traders, so that ‘Sharma Traders’ and ‘SHARMA TRADERS.’ become one string. Keep phone numbers as digits only.
Flag exact matches on tax id as certain, and name matches with different tax ids as doubtful. A person reviews the doubtful list.
- Exact tax id match
- Same phone number, different spelling of the name
- Same email domain and address
- Names that differ by one or two letters
- Names that look alike but have different tax ids
Merge head office and branch accounts with care
Decide the shape first. Many firms keep one parent account with child branches, each holding its own ship-to address and contact, while others keep separate accounts because each branch pays its own bills.
Merge branches only when they share a tax id and a payer. A branch with its own credit limit or invoice series stays a child account. No exceptions.
Write the rule on a page and apply it to every branch. Mixed rules produce a list nobody trusts.
- Parent account with shipping branches
- Separate credit limits stay separate
- Different tax id means a different party
- Contact persons move to the surviving record
Keep opening balances on the right party
Duplicates are dangerous because balances split between them. One entry holds an unpaid invoice while its twin holds an advance that should have offset it, and nobody sees the pair.
Merge the balances onto the surviving record and keep the invoice-level detail, not only a total. Agents need to see which bills are open and how old they are.
For vendors, check advances and debit notes before merging. A vendor with two records can hide a refund you are owed.
Import a sample and tie it to the old ledger
Load fifty parties, chosen to include a branch group, a merged duplicate and a vendor with advances. Open each new record. Compare it with the source row.
Then compare totals. Total receivable and total payable in the new system should match the old ledger on the same cut-off date. Any difference is traced to a party before the full import.
Freeze changes in the old system at cut-off, run the import once and sign off the totals. Do the sample import this week.
Duplicate review sheet
A spreadsheet with one row per suspected pair. Columns hold source row, name, tax id, phone, address, match basis, decision (merge, keep both or branch), surviving record, balance carried and reviewer initials. A totals tab sets receivable and payable beside the old ledger.
Open a blank worksheet to printQuestions owners ask
Should I merge duplicates automatically?
Only exact tax id matches. Everything else deserves a human look, since two similar names can be two different firms.
What if a customer has no tax id on file?
Fall back on phone, email and address, and mark the record as unverified. Fill the tax id in when the next invoice is raised.
How do I treat a firm that is both customer and vendor?
Most systems hold one party with both roles. Merge them only if the tax id matches, and keep the receivable and payable balances separate.
Do I need to clean old inactive accounts?
Import them as inactive if the history matters for audits or disputes. Leave out those with no balance and no activity in years, after you archive the original file.