MRSware Sales and Commissions
  • MRSware
    • About
  • Sales & Commissions
    • Sales Reps
    • Manufacturers
  • CRM
  • Blog
  • Pricing
  • Support
    • Training >
      • Training Classes - MRSweb
      • Training Classes - MRSware >
        • Lesson 1 - MRSware Home Page
        • Lesson 2 - CRM - Companies/Contacts
        • Lesson 3 - CRM - Calendar
        • Lesson 4 - CRM - Journals
        • Lesson 5 - CRM - Tasks
        • Lesson 6 - CRM - Projects
        • Lesson 7 - MREPS - Customers
    • Documentation
    • Integrations
    • Admins
    • Live Service Status
  • Login
  • Free Trial

When Account Numbers are Not Unique

7/25/2020

0 Comments

 
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.

Make Them Unique

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:
10000 ACME Aube
10000 Burn Seat
10000 Carm Camb
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.
​

Don't Use Them

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).
​

What to Do?

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.
​

The Offer

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.
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    MRSware Blog

    contains updates for shows and conferences we will be attending, product releases and enhancements (the changelog), and general market engagement articles

    Categories

    All
    Account Numbers
    Analytics
    Apple Mac
    Calendar
    ChangeLog
    Conferences
    CRM
    Customers
    Data Analysis
    Database Features
    Data Import
    Email
    File Share
    Free Community
    Invoices
    IOS
    ISSA
    Marketing
    Mobile Computing
    MRSware Desktop
    MRSweb
    New Features
    Outlook
    Passwords
    Payments
    Permissions
    Projects
    Reports
    Sales
    Sales Force Automation
    Sales Import
    Sales Reps
    Scheduling
    Security
    Sync
    Timely Commissions
    Tips & Tricks
    Tools
    Updates
    Web Client

    Archives

    February 2023
    January 2023
    October 2022
    August 2022
    July 2022
    June 2022
    May 2022
    April 2022
    December 2021
    October 2021
    September 2021
    May 2021
    February 2021
    July 2020
    June 2020
    May 2020
    April 2020
    October 2019
    September 2019
    June 2019
    April 2019
    August 2018
    March 2018
    January 2018
    December 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    October 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    June 2015
    May 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    July 2014
    June 2014
    May 2014
    April 2014
    January 2014
    December 2013
    September 2013
    July 2013
    June 2013
    April 2013
    March 2013
    February 2013
    December 2012
    August 2012
    July 2012
    June 2012
    May 2012
    April 2012
    March 2012
    January 2012
    December 2011
    February 2011
    January 2011
    December 2010
    April 2009
    May 2008

    RSS Feed

Home | Sales and Commissions | CRM | Blog |​ Pricing | Training | Support | Log In | Free Trial 
sales@MRSware.com     (866)795-8386 x1
Copyright (c) 2002-2023 EYOND, Inc
.
MRSware is an EYOND, Inc. Integrated Solution
made in Austin, TX  USA