Eliminate duplicates, and concatenate values from one column across six spreadsheets

Copper Contributor


I'm trying to import data into a CRM (i.e. contact info.). The columns are: 

Contact TypeFunds Invested InCompany NameDBAFirstLastRIA1031FundAddressSuiteCityStateZip CodePhonePhone 2Email

The info is spread out across six spreadsheets but I've made sure all of the column headings are identical. These folks are all financial advisors. Many are repeated across the spreadsheets. The thing that defines each spreadsheet is the Fund type. For example. all of spreadsheet one is Fund 1. All of spreadsheet 2 is Fund 2, etc. Many advisors advise on multiple funds so their name (as I said before) is included in multiple spreadsheets.

The goal is to get one clean list showing the contact name only once, but if they advise on Fund 1, Fund 2, etc. That info is concatenated in one column (i.e. Funds Invested In) and formatted as such: FUND 1; FUND 2; etc. This will allow me to import the spreadsheet into the CRM which will appropriately populate the CRM property "Funds Invested In." (It's a multiple choice select function.)

I've gotten pretty close using power query. I can certainly use it to create one big spreadsheet, but then we lose out on the fund info (i.e. it will only show a contact tied to one fund rather than all of the ones they advise on.) I've also explored merge but can't get the concatenate functionality/formatting. 

Thank you for any thoughts. I hate when it seems like something should be so easy and becomes so complex. Also - the unique ID for each contact is their email address (I've confirmed all addresses are unique).

1 Reply

@Marian253 Without some realistic data, it's difficult to help. Can you share a scaled down sample of your file with the queries you have made already? Replace any confidential information with fake information, but leave the structure of the tables as they are.


Upload you file to Onedrive, Dropbox or similar and share the link that gives access to the file.