Using Alteryx-Python , generate output excel file in attached format from the input file

sanky1990

Hii

1) Looking for Alteryx workflow or python code assistance for converting input excel file into output file in attached format. 

  'Notes' column is the 1 where i need to break cell value into multiple rows (each starting with date:) and also new column with only date.  Then other date columns there are some format changes required  and then finally a merge for each of other columns as per given in output file, 

(Need to make sure output file is generated in same formatting as attached output file)

Attached files:

excel input file: Input_to_python.xlsx

excel output required:  Expected_output.xlsx

2) Also looking for reverse logic (alteryx or python or both), meaning if my input file is 'Expected_output.xlsx'  and i have to now generate output file as Input_to_python.xlsx

Expected_output.xlsx

Input_to_python.xlsx

Yoshiro_Fujimori

Hi @sanky1990 

Splitting a cell with multiple lines can be achieved with one [Text To Column] tool with [Split to rows] option as below.

However, [Expected_output.xlsx] contains merged cells, which is not easy to achieve on Alteryx (or even on Python),

because the data is "unnormalized".

If the output is to be used for further analysis, I would keep it as "First normal form".

As a bonus, your work to "reverse" the data format would be much easier.

If you still have to comply with the original format with merged cells,
I think it would be better using VBA (though I am not familiar with it).

SplitToRows.yxmd