Dynamic Column Name with Merged Cells

bburzanko

I have the sample file attached that has Year / Month / Actual-Budget information. I am trying to get to a point to have the column names pulled from the first 4 rows to have the column name be 2023-2-November Actual, 2023-2-November Budget, 2023-3-December Actual, 2023-3-December Budget, etc...

The merged cells are causing an issue for me to allow for this and the periods change so I cant just manually rename.

Has any one dealt with this issue and what was the work around you used for it?

The file attached has the input and end goal.

Sample Close File.xlsx

ccanterman

Hi! Take a look at this and see if it works. 

What I did was separate the data into the first 4 rows = headers and then skipping the first 4 rows

1. Set the Input Tool to have "first row contains data" so the column headers are called F1, F2, etc, and the years starts in Row 1
2. Sample Tool to get the first 4 rows which contain the different column header values
3. Flip the first 4 rows into columns
4. Use Multi-Row Tool to fill in nulls - this is the trick to get around your merge problem
5. Summarize to Concatenate based on Column # (F1, F2, etc)
6. Put the now-single-column of column headers into a row so it could be Unioned with the rest of the data
7. Dynamic rename to take the now-single-column of column headers into the proper column header position

New Workflow2.yxzp