back to knowledgebase

Sort, dedupe & merge import data

Updated: 22 February 2019 12:31:06 Windows::Excel OpenCRM::Data Import-Export

If you have a multi-select picklist in OpenCRM that you want to populate with a data import, you will need to have only ONE row per individual or OpenCRM record. Often, people will have this data held in a way that requires the concatenation of multiple rows into a single row, as below. 

Scenario:

You have multi-select picklist values for one record on multiple lines, e.g. John Smith has skills in Sales, Marketing and Accounts, and Jane Jones has skills in Sales and Marketing, and so you need to bring this information onto one (comma-separated) row in order to import the data.

  A B C D
1 FIRST NAME LAST NAME EMAIL INTEREST
2 John Smith jsmith@faq.com Sales
3 John Smith jsmith@faq.com Marketing
4 John Smith jsmith@faq.com Accounts
5 Jane Jones janejones@faq.co.uk Sales
6 Jane Jones janejones@faq.co.uk Marketing

 

You need to add two columns, in this example we have added column E which contains the values we want to import, and column F to allow us to change the sort order of the data:

  A B C D E F
1 NAME LAST NAME EMAIL INTEREST CONCATENATED ID
2 John Smith jsmith@faq.com Sales Sales 1
3 John Smith jsmith@faq.com Marketing Sales, Marketing 2
4 John Smith jsmith@faq.com Accounts Sales, Marketing, Accounts 3
5 Jane Jones janejones@faq.co.uk Sales Sales 4
6 Jane Jones janejones@faq.co.uk Marketing Sales, Marketing 5

 

The data in column E is created by pasting the following formula into field E2: =IF(C2=C1,E1&", "&D2,D2)

The formula is searching for a match in column C, so where it finds a match, it will concatenate the values from column D into columnn E. You therefore need to adjust the formula you use to match the columns which you are using to look up duplicate records, and the columns from which you want to copy the data. 

You then need to add an incremental number into column F. This will allow you to sort the list into reverse order, allowing you to dedupe the list which which return you with a list like this:

  A B C D E F
  NAME LAST NAME EMAIL INTEREST CONCATENATED ID
1 Jane Jones janejones@faq.co.uk Sales Sales, Marketing 5
2 John Smith jsmith@faq.com Accounts Sales, Marketing, Accounts 3

 

It is important that you SAVE and exit the csv file, before reloading the page to strip out the formula in column E, to prevent the information reordering itself.

 

Rate This Article
  • 1 star
  • 2 star
  • 3 star
  • 4 star
  • 5 star
Feedback and Comments
captcha code  


You may also be interested in: