Data Management in Excel

New Contributor

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

6 Replies

Could you post a sample of the file so I can see how it's structured. Sounds like something that Power Query can resolve.

Hi @coughlanconsulting


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





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


Ah, yes unless there is some sort of pattern / logic / consistent identifier automating a solution becomes next to impossible and good old manual re-keying becomes the best approach.

You might try Pivot Tables in Excel.


@rayeverest,  Pivot Tables will be of no help in this situation unfortunately.  They work well with structured columns of data.

Related Conversations
Creating Pie charts.......
dougler2020 in Excel on
2 Replies
How to use the trendline to find percentage change?
fishfish31 in Excel on
0 Replies
Noemie911 in Excel on
4 Replies
Summarzing data
Ravi_Kumar in Excel on
1 Replies