It Worked!

"I just wanted to provide you with the link to the blog post I did today for our company's blog: http://www.divitaspeaks.com/ in which I talked about the Quickbooks 14500 club. I have happily decreased my customer master file by half - thanks to you and your wonderful program. Thanks again for your help."
Benecia Beyer - Accountant
DiVita & Associates, Inc. www.divitainc.com

Thanks Mark. We have a lot of repeat customers whose information we probably lost but we were able to cut our customer list down by thousands. I'm glad I found your program!
Regards Jack & Debbie LaBrake - LaBrake's Garden Path & Pond LLC - www.gardenponds.com
ANSWER: No Information was lost in this process - Check the "Retail" account and you'll see that everything's there. Besides, you've got your backup.

Quick Summary

Here is an example:

Your Current System Looks like this:

Customer #1: Abe Adams (Inactive)
Invoice # 123 - Sold to Abe Adams, 123 west Street, NY, NY - 10 units at $5.00 each

Customer #2: Bill Barnes (Inactive)
Invoice #124 – Sold to Bill Barnes, 456 East Street, LA, CA - 15 units at $4.00 each

Customer #3: Cathy Clark (Inactive)
Invoice #125 – Sold To Cathy Clark, 789 North Street, Kansas City, MO – 20 units at $3.00 each

After Merging Customers Your new system will look like this:
Customer #1: Retail (Inactive)
Invoice #123 - Sold to Abe Adams, 123 west Street, NY, NY - 10 units at $5.00 each
Invoice #124 – Sold to Bill Barnes, 456 East Street, LA, CA - 15 units at $4.00 each
Invoice #125 – Sold To Cathy Clark, 789 North Street, Kansas City, MO – 20 units at $3.00 each

What you will Gain:
Your file size will be the same, but you will have less than 14500 records.

What you will lose:
If Cathy Clark calls and hasn't done business with you in over 2 years (and was, therefore, marked inactive"), instead of clicking on her name in the customer list and seeing all of her purchases, you will need to search the invoices under the customer name "Retail" for her name or invoice number.
- Or –
Make a backup of your existing company file and restore it to a NEW company called <Company Name> - OLD DATA. If you must look up a customer, them up in the OLD DATA company, find the invoice number you want, then switch to the new company and get the information you need.

 

Here's the way it works – Full details in the manual

Back up your data. See (Restore to an old company, below, for a great tip.)
1 – Print out a Sales Report Summary for the last two years. Export it to a worsheet called NewCust in a Workbook called QB14500.xls.
2 – Export your entire customer list into the same worksheet, QB14500.xls on a spreadsheet called AllCust
3 – Use the formula I provide to mark everyone who is NOT on the NewCust Sheet as not-Active
So everyone you have not done business with in two years in not-Active. The formula can be found in the manual or in the sample spreadsheet.
4 – Import the AllCust sheet (changing old customers to not-Active). Detailed Instructions in the manual.
5 – Open the Customer List
6 – Use the Pull Down to show All Customers (Active and not-Active)
7 – Sort on Active.

Now, you have all the inactive customers at the top of the list. Start the Script and it will work its way down the list.
 

The program is called a "Keyboard Macro". When it's running, as far as Quickbooks is concerned, it's as if someone is typing on the keyboard.

If you go to the customer screen and type the keys that cause two customers to merge -- you'll be doing exactly what the script does.... over and over again... starting at the top of the customer list and working it's way down.

There is some error checking in the script to handle some of the common errors that could happen. It's all in the documentation.

In oredr to get all the names to be merged at the top of the list, the documentation outlines a procedure for exporting your customer list to Excel and automatically changing older cusromers to "Inactive."  This way, the inactive customers are at the top of the list and we can work our way down.

Typically, you'll merge 500 or so customers every night.

Try the Free Demo, which will merge 10 at a time. You can merge both Customers and Vendors.

A version to merge Items is available as well.

Restore to an old company:
Although you don't lose any data, old, merged, individual accounts can be harder to find. Here's what to do. After you make a backup, Restore the backup to a newly created company so you have "Your Company" and "OLD Your Company". Run my program on the "Your Company" files. If you need to look up old information -- just go into the "OLD your company" files to get the information you need. Put a password on the "OLD Your company files" so it doesn't get used by accident.

 


Frequently asked questions:

Q. Can this program corrupt my data?

A. No. Remember, the program works like an automatic keyboard. This program "types" for you.


Q. Will it affect my financial statements?

A. No. This program Merges accounts. Financial data remains unchanged.


Q. I need an excel formula the makes all customers prior to 12/31/2008 with a zero balance inactive. Would you please provide us with an Excel formula that will give us that result.

A. The key to your solution is not in Excel, but  in creating a report in Quickbooks of all the people you want to KEEP. You can create a report of the customers mentioned below and export that into Excel, into the worksheet called "NewCust".

Alternately, if the report criteria is complicated, you can create two separate reports

1.       All customers with sales since 2009, Export to worksheet NewCust

2.       All customers with open balances, all time, Export to worksheet NewCust1

Then copy the rows on NewCust1 to the end of NewCust. The fact that many of the names appear twice on the NewCust does not matter. The formula says "If you find this name anywhere in NewCust, then keep it active."


 

Your program worked PERFECTLY! Thank you so much for having the smarts and making the effort to build this program. We upgraded all our software and computers, only to have the POS integration fail because of our 10,000 customers + vendors and items exceeding the 14,500 limit. All Quickbooks said was too bad, upgrade to Enterprise, which was totally unnecessary for us...a small chocolate/candy store that uses Quickbooks Point of Sale and never should have been transferring customers to Quickbooks in the first place...totally not needed. Thanks again. Probably the best $75 this store has ever spent!

- Tim Downey www.4somethingsweet.com

You are a GENIUS!! Thank you for coming up with that script. I have successfully merged all my customers so I can now import new orders into QB. This has been a tremendous help.
Discount Catholic Store, Inc, Charlotte, NC 28273
[Thanks, but if I'm so smart how come I'm using Quickbooks?]

  Hi Mark,
I just finished using your program to reduce the size of my Quickbooks customer list and would like to say that it works exactly as you advertise. I would recommend it to anyone that runs into the 14500 Quickbooks customer limit. I was not about to spend $3,000 to upgrade to Enterprise just because I am unable to remove old customers. I own a rapidly growing company (www.landairsea.com) and can find other more important places to spend $3,000. Thank you for a great solution to a problem that many Quickbooks users will eventually face.
Best regards, Rob WagnerPresident - LandAirSea Systems, Inc
http://www.landairsea.com