Excel/Google Sheet data clean up

I have a Google Sheet where I need complex data clean up.

I will provide the logic. Professional will have to transform it into excel formula in order to clean up the data and have it ready in other clean sheet.

Please apply only if you are an expert in Google Sheet/ Excel formulas