When Account Numbers are Not Unique

There are a few cases where manufacturers will send out sales and commission reports with Account Numbers that are not unique, with more than one customer or location represented by a single number.  Some examples include:

• Multiple branch locations, such as individual retail locations with a central billing location.
• Trace sales reports that show the End Users to which goods are shipping.
• Design house that uses contract manufacturers in their OEM products.

For these case, the Account Number being used by the manufacturer doesn't track to the location within the Rep Agencies territory, or necessarily to the same sales rep as might be assigned the main billing location.  It is, of course, important to get the correct location and correct sales rep for each Invoice, especially in cases where bonuses or split commissions are paid based on those sales.

One option to handle this situation is to make those account numbers unique for each distinct customer or location.  This is pretty easy to do in a spreadsheet, using a formula to concatenate (bunch together) a few columns to create a unique entry per customer.

Example 1: Account Number (not unique) + Customer Name + City
Given that you have some columns that describe the customer, such as parts of the address, some name for the location, and possibly a non-unique account number that you are trying to work from,  combine those parts into a unique whole.  The formula you would might look like this:​

= A2 & B2 & C2

This is a pretty easy entry to make.  Its function is to take what is in column A, row 2 mash-up column B and column C in the same row.  You would then copy that formula to each row as a new column.  The result will look like this:

10000ACME Corp.Auberdeen
10000Burnett IncSeattle
10000Carmichle TechnologiesCambridge

Add a little bit more to the formula to clean it up and make the account numbers you are creating more readable:

= A2 & " " & LEFT(B2,4) & LEFT (C2,4)

And you will get a cleaned looking set of unique account numbers, more like this:

Of course, there are lots of variations and possibilities here, based on what columns you have available to you already, and how you want those account numbers you are creating to look afterwards.  The key is that you are creating a consistent, unique number that you can use each month as new spreadsheets come in.
​

Option 2, of course, is to not use the given account number at all.  This is easier to do from not having to do anything in the spreadsheet before import, but more time-consuming on cleaning up the Customer entries after import...  After import, you don't want to have a whole lot of duplicate customers (use the Duplicate Removal Wizard to solve this one), and likewise don't want to have a whole bunch of numbers mashed into a single entry (use Conflict Resolution to resolve these).
​

How you decide what to do usually depends on the volume of customers that need to be adjusted.  If a small number have the same account number, you might handle that easily through Conflict Resolution after import.  If a large number share an account number, and that is going to be a consistent case moving forward, it will be easier to create your own unique numbers in a consistent way, and use those each month.
​

We regularly do, and are always happy to, take a look at some samples of what you are getting from your manufacturers and make recommendations based on the actual data.  Feel free to give us a call or send over your samples (doesn't need to be the whole sheet, just a few rows to see the layout and content) to support.