Jan 10 2019 07:58 PM
I have a business with large amount of information prepared by a staff member - client and prospect names, key personnel within that business, their addresses, their emails, their phone numbers etc etc. Unfortunately I find that all this information has been entered in Excel in the one column under a heading for each client/prospect. I need to get that data out into columns beside each name. Have been able to use the filter function to get the data into separate columns but can't then link/associate them back to the original first column to have them all in the one row. Have tried filter/sort/range/vlookup options but nothing is working. Any assistance appreciated
Jan 10 2019 08:54 PM
Jan 10 2019 10:35 PM
I've attached a an Excel File containing a Power Query routine that should help solve your issue
You will need to go into Data > Queries and Connections and then double click on Clean Data in the right hand pane to open up Power Query
Click on the cog next to Source and pick your file name that contains the messy data
Also I've assumed the data is on Sheet1, if not click the cog next to Navigation
Then click on the last step "Removed Columns 1" and you should see your data cleaned up. Then click Close and Load
Jan 11 2019 05:09 PM
Hi Wyn
Many thanks for the information. I can see how this would be a great resolution to my problem, assuming there was good and logical source data. Unfortunately when I look further into the detail of the report not all of the rows under each client/ prospect name start with the same identifier, some clients have different rows of details with multiple addresses, or multiple key staff or email addresses etc i.e. the simple version I provided to you doesn't reflect the complexity of the actual source data. As a result the transformed report then gets out of order. Appreciate the assistance but maybe all the errors and issues in the set up are going to defeat our best efforts
Jan 11 2019 05:23 PM
Jan 12 2019 03:25 AM
@rayeverest, Pivot Tables will be of no help in this situation unfortunately. They work well with structured columns of data.