Assign a merged column as column name to all rows

[murthybhs]

I have multiple files that have the format below:

Each file has a different "Start Date". I would like to assign "Start Date" as a column to all the records in the file and assign "Department" as a column to the employees that belong to a particular department. I want to consolidate all the files in the folder (I've been using wildcards in Input Data tool) and want my data to look as below: Note: Other files have a different Start Date and Department names.

I've been trying all kinds of things since the past few days but nothing seems to work. There might be some logic that I am missing. Any help on this is highly appreciated. Thank you everyone in the community for your constant support.

[apathetichell · Accepted solution]

You use a multi-row formula which matches each value which contain department. it replicates that value in the new field and carries it down row to row until the new value is found.

Attached  is the basic logic of how it works.