A reader writes in to ask: “I maxed out at 14,500 combined customers, vendors, etc. and I don’t want the extra cost of purchasing Enterprise so I want to merge customers for older years. I tried to export the customer list to Excel, change names, and then import back, but they didn’t ‘merge.’ What’s the best way to do this?
This is a simple one. Our friends at www.Q2Q.us offer a utility called “Merge Customers” that allows you to merge customer names and their related transactions into a generic customer. The program will locate customers with no activity in a user specified date range and then merge those customers into a designated generic customer. I have used this Utility multiple times, over past years Karl would send me a license to check-out some tweak he did, although I can’t remember when it didn’t work right from the very start.
Prior to starting this process insure that you have a current, fully verified, back-up of your data for historical archive purposes. Once you have installed and launched the Merge Customers utility, make the SDK (integrated applications) connection to the QuickBooks File you wish to change.
The next step is to select a date range you wish to review, then select the option to “Build Report.” The Merge Customers Utility screens through the list of customers and procures a ‘No Sales’ report for Customers covering the period you specified. After reviewing those Customers, you can then select a Customer which will act as the ‘target’ for the merge. When you click the ‘Merge Customers’ button, the utility proceeds to merge each customer from the report into the target Customer. You need to remember that this process is permanent, you can’t undo it once the merge has taken place (that’s why you backed-up first). The Utility will also prepare a status report that shows you the results of the merge process.
Murph’s Best Practice about Date Ranges and order to proceed. The tendency is to think that you want to work from oldest date range to most recent, but in reality the opposite is true. You are trying to identify those customers who haven’t done business with you in recent times. Thus work backward using the most recent date range first, then the preceding date range, and so on. There is only one thing to watch out for, if you enter ‘prospective’ Customers in your regular Customer Center as opposed to the QuickBooks Lead Center, you will need to exclude these potential future Customers during this process.
My 2nd recommendation is to ‘crawl’ before you ‘leap’. Don’t say, "well I think I will just take the entire range of dates from first day I used QuickBooks until today." That won’t work because almost all of your customers will have had transactions at some point during the entire time line of your records unless they were names you imported from a prior file. But even if it did work, you could have a huge list of names; all QuickBooks utilities (at least as far as my experience goes) tend to work better in ‘little bites’, so like a big shark take a nibble at a few dates first, and then go back for subsequent ‘dates’ (bites of data).
Just two more things….always, always, always, perform a fully-verified back-up before you start, back-up between each ‘date range’ selection and merge, and do a back-up after you have finished the process. If any one of the back-ups fail to fully-verify, STOP, run Verify, and review the QBWin.log file, and if appropriate, resolve any data issues (including use of the Rebuild utility) before continuing.
This utility not only simplifies but streamlines the process of merging potentially thousands of names to rid yourself of the limitations of the 14,500 name wall in QuickBooks Pro and Premier.
Graphics Source: www.Q2Q.us