AppGarden Import Cleaner for Sage logo AppGarden

Import Cleaner for Sage™

Duplicate customer and supplier account references in an Excel™ list

By Paul Kemp · Published

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

Find them by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Add a helper column that cleans the reference: =UPPER(TRIM(SUBSTITUTE(A2,CHAR(160)," "))).
  2. Add a count column: =COUNTIF(B:B,B2). Anything above 1 is a duplicate.
  3. Filter the count column for values above 1 and sort by the cleaned reference.
  4. For each group, decide: is this a repeat, or two different accounts that need different references?
  5. Alternatively, select the cleaned column and choose Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to see them in place.
  6. 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.