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 | 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 | 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 | 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.
Need More Help?
If you have any questions, just start a Live Chat, "Click" on the Chat Icon in the lower right corner to talk with our support team.