Duplicate customer and supplier account references in an Excel™ list
A customer or supplier list exported from Sage™ might hold the same account reference twice. A duplicate can be a harmless repeat or two different accounts sharing a reference. Either way you want to know before you build on the list.
Why duplicates sneak in
- The same reference typed twice by two people.
- Differences in case (
ACME01,acme01). - Hidden spaces, including the non-breaking space, which
TRIMwill not remove. - A reference that lost its leading zeros when the file was opened as a CSV, so
0040and40now look different, or two accounts look the same.
Find them by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy.
- Add a helper column that cleans the reference:
=UPPER(TRIM(SUBSTITUTE(A2,CHAR(160)," "))). - Add a count column:
=COUNTIF(B:B,B2). Anything above 1 is a duplicate. - Filter the count column for values above 1 and sort by the cleaned reference.
- For each group, decide: is this a repeat, or two different accounts that need different references?
- Alternatively, select the cleaned column and choose Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to see them in place.
- Only after deciding, use Data → Remove Duplicates on a copy.
Do not delete rows before you have compared the other columns. A "duplicate" with a different balance is not a repeat.
Flag them with the rest of the export
Import Cleaner for Sage™ lists duplicate customer or supplier account references as one of its export checks (SE-08), with the cells named so you can select them. The scan and every explanation are free, and the workbook stays on your machine.
See Import Cleaner for Sage™ for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Sage is a trademark of The Sage Group plc. Import Cleaner for Sage is an independent product, not affiliated with or endorsed by Microsoft or The Sage Group plc.