Any idea on how to merge duplicates ?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am using QB as a CRM with contacts attached to companies, operations attached to companies, and sales attached to companies.

Unfortunately there are many duplicate company records. So the manual operation we do is to reassign each contact to one of the companies, ad same with all the past operations and all the past sales . Because there can be many children records to the company record, this is very time consuming

Has anyone an idea how to automate this process ? Like coding a button to reassign all children records to a new company record ?

Thanks
Photo of Patrice

Patrice

  • 10 Points

Posted 4 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Patrice,

From what your describing I think it would be quicker to do this in Excel and import the clean data (as it sounds like its a one off task).

Here is the steps I would take (assume for the example below that a company has something unique like a customer number although you could do it with a name):

Export your companies table, copy the data to two worksheets. In worksheet 1 delete all the columns except for the one that has your unique identifier. Then highlight the column click remove duplicates.

Then in the next column do a match and index formula for example =INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),1)

This returns the figure in column B which could be the record ID column for the company.

This means you should only have one company record with a record ID listed for every instance.



Now your other datasets. Ensure in your table to table relationships you create a lookup field in the related table which shows the unique identifier for the company i.e. customer number etc. which enables you to export both the unique identifier for the company table and the record id/primary key for each table (child tables)

Export the info, then paste in the company info (from above) to worksheet 2. Then using a similar variation of the same formula =INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),1) match the children against the company list and index pulling the record ID/Key for the parent.

This means if you map this column in your import to related company field, it will remap the children. Be sure to also upload the key/record id of the children to ensure it updates records rather than creates new ones.

You can do this for each table related to companies.



You can then create a summary field counting the number of related children to each company, then create a table report showing all which have 0. These should then be the companies you need to delete or new ones that haven't got any related records yet.

IF your nervous about mass updates then backup your data (table exports) or copy the applications and its data and try this on the copy first.
Photo of Patrice

Patrice

  • 10 Points
Thanks for your answer. That was what I was afraid of doing, specially because we have unfortunately no company unique identifier... But it is nevertheless doable so.
Photo of Jack

Jack, Champion

  • 50 Points
Don't be afraid, the key thing to avoiding any data loss is to back up or copy.

You could use something else to match your records, or make several passes at trying to eliminate your duplicates. The obvious one is company name, once you have done that one and updated all your records, then run the process and try something like company number or VAT number or some other unique thing like phone number, email address, anything really, it may be that you do this process many times on different fields within your company data to get rid of all the duplicates. When you have removed all the duplicates try creating a formula text field called Company ID using the record ID as a basis, a formula such as "V"&Right("000000" & ToText([Record ID#],6) This will give you supplier numbers based on a prefix of V and then the record ID with leading zeros so for example a record id of 1234 would appear as V001234.

Hope that helps.
Photo of Patrice

Patrice

  • 10 Points
Yes, we definitely need to do that ! Thanks again
Photo of Dyn

Dyn

  • 346 Points 250 badge 2x thumb
Adding on to this, what if the company names are in different formats? Eg. ABC INC, Abc Inc, AbC
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Either before or after the export you can do a few things.  Make them sorted A-Z, also make them all Lower case, or upper, or proper.  

Excel duplicates can catch most, but you might have to do some eye ball checks to confirm.