Quick Summary
|
Here is an example: Your Current System Looks like this: Customer #1: Abe Adams (Inactive) Customer #2: Bill Barnes (Inactive) Customer #3: Cathy Clark (Inactive) After Merging Customers Your new system will look like this: What you will Gain: What you will lose: |
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."