How to prep a file by shifting some details new rows

[cgoad61]

I am creating an Alteryx flow to organize some fields in a report that are currently in several columns but includes duplicates on some rows that are only specific to certain columns.  I'm thinking the best way to resolve this is to somehow prep the file as I've noted in the attached pic so I can remove the duplicates up front.  Can anyone help with how I can do this?

[binuacs]

@cgoad61 Can you provide a sample input file and expected output?

[cgoad61]

Thanks binuacs... Here is an example source file and an Alteryx flow showing the final except it has duplicate items within the concatenated fields.  Because of the way the source report was built, the Unique Tool didn't work.  This is why I thought of trying to split out the details like the lower part of the image I had attached.  This would create additional rows but would allow for removing duplicates without risking loss of other details that are needed. 

Technically I would've also preferred the concatenation to be presented more cleanly on my final report but I don't see how that is possible.  As an example... for Main ID 103 the Concat_SOX FSLI/FN & Fin Stmt Elements column would reflect:

Income > Interest Income; Balance sheet > Other Assets, Loans

instead of:

Income > Interest Income; Balance sheet > Other Assets; Balance sheet > Loans

--  So the 1st field in the 4 concat groups is only referenced once for the corresponding items in the 2nd field.

[binuacs]

@cgoad61 what is the expected output for the below main ID 103?

[binuacs · Accepted solution]

@cgoad61 Something like below?

[cgoad61]

Thanks Arcturus!  This is great and produces the final report I would want to see but I'm fairly new to Alteryx so Macros are rather over my head at this point.  I see that 103 is referenced in the macro and I'm assuming its used as an example that is applied across the report.  The actual report I'm working with is much larger and I'm not clear on how to update the Macros (it looks like there is one within the other) to work with my report.

FYI - The details in the report are updated on a regular basis and the workflow would be run daily.

[binuacs]

@cgoad61 the workflow based on the given input file, if your input files are having different format then the workflow might not work, also without the help of batch macro it is very difficult to achive your result.

[cgoad61]

Thanks again Arcturus.  It's not actually a different format... the field name will be CER ID instead of Main ID and the ID #s would be different.  It would also have hundreds of line items.  I appreciate your assistance.