Hi
I have the attached excel workbook and it's not in a great structure, I need to format it to remove unnecessary columns and give the data headers and transpose etc. Can anyone help me please?
There is a tab in the sheet showing the excel unstructured data, then another example of how I would like it to look (example tab) . Within the example tab I have only shown the first few fields, I would still require all data on the unstructured sheet.
Many Thanks in advance.
Solved! Go to Solution.
The Keys to parsing in unstructured data:
The attached workflow I started for you should be a good jump on point to continue with parsing the remaining rows.
Great, thanks for this I will take a look :)
this is great, thanks.
I'm struggling to get my transpose to work at the end? I basically want to be able to 'anchor' (so to speak) the name and date of FA, then having the remaining columns as headers with the data in the same row as the name. (example with dummy data below)
So final output is;
Name Date of FA income support benefit job seeker etc etc
Jo Bloggs 14.01.2018 10.00 100.00 200.00 300.00 400.00
Made a new version of the workflow with having only 1 row with many fields. And the key tool used now is the "CrossTab" Tool which is much easier to understand than the "Tranpose" tool. Good luck with the rest of the fields and I think you can copy sections of code around to process more of the rows. Attached is version 2 of the Workflow with just one row with many fields.