Data Management in Excel

Copper 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
Hi

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

 

 

image.png

 

Click on the cog next to Source and pick your file name that contains the messy data

image.png

 

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

Hi,

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.