Help to consolidate data

Copper Contributor

I am forced to move into a new CRM and the tags I had on prospects and customers in the old one would not download all the tags I had for a single name into one line. I had to download each TAG list into its own spreadsheet with the tags listed in sperate columns. 

I have consolidated all the separate tag list spreadsheets into one master list and have sorted by last name - so now I can see all the same name listed on multiple rows with their corresponding column tab name. 

Is there a formula or method of consolidating the names into one line while pulling in the separate column tags into the one name to make the download into the new CRM easier? 

Thanks in advance! 

2 Replies

@Curt_Coenen 

Unless someone else has some inspired insight, I doubt that your query is specific enough to allow anyone to come up with a solution.  That said, Power Query is the MS tool specifically written to support ETL (extract-transform-load) tasks such as you describe.

@Peter Bartholomew Thank you Peter, I will investigate. 

To hopefully be a little clearer and more concise: I have a list of multiple names, of which had to be downloaded in separate spreadsheets that listed their identifying tags like location, activity, contact person, interests, etc. 

Since they had to be downloaded separately by these ID tags and consolidated into one spreadsheet, the 714 total names are each listed in a separate line by the ID tag that they had attached to them, now totaling 2390 lines of data. So, some may only have one or two entries, others may have up to 6-8 entries. Bob Jones may be listed twice with a column named for his city and sport, while May Smith may be listed 6 times with column tags of city, sport, age, veteran, activity and contact person.

I want to be able to consolidate the multiple listing of each name into one line and consolidate the different data columns information into that one line and eliminate the repeats so I can upload the condensed data into a new Customer Service Management (CRM) system.

If you or any others have a suggestion, I welcome any and all! In the meantime, I will investigate the Power Query option.

Thank you for your quick response yesterday. Much appreciated!

Curt