Dynamic Header Rename

[Kerry]

I have a set of data that looks like this:

Total Panel	F4	Income Under $20000	F6	Etc.
01/03/14 - 01/02/15	01/03/15 - 01/02/16	01/03/14 - 01/02/15	01/03/15 - 01/02/16	Etc.
Data	Data	Data	Data	Data
Data	Data	Data	Data	Data

So, I would like to change each header name that contains "F*" to the column header before it. So that the header names will be different, I would then like to move up the dates that are below each header and either add that date as a prefix or suffix.

Thanks!

KT

[BenMoss]

Hi kerry,

Just for clarity would you also be able to post a copy of the desired output of your table given the initial table provided?

Ben

[Kerry]

Sure, here ya go:

Total Panel 01/02/14 - 01/02/15	Total Panel 01/03/15 - 01/02/16	Income Under $20000 01/02/14 - 01/02/15	Income Under $20000 01/03/15 - 01/02/16
Data	Data	Data	Data
Data	Data	Data	Data
Data	Data	Data	Data

Again, I don't care if the Dates come before or after the Header Name, just so they are included.

Thanks!

KT

[jdunkerley79 · Accepted solution]

I think something like the following should work for you.

This is based on the Total Panel, F1, ... being the Column headers

- Sample the input to keep just one row

- Use a Transpose tool to rotate to Row based Name, Value

- Use a Multi-Row Formula to replace the F* names and concatenate the Value on the end:

IIF(Regex_Match([Name],"F\d+"),[Row-1:Name],[Name])+ ' ' + [Value]

- Skip 1 row of the input data

- Use a positional dynamic rename to rename the data having skipped one row

Sample workflow attached