This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
One solution would be to use a couple Multi-Field Formula tools to write all your formulas, and then the Dynamic Rename tool to get the correct Field Names. Not sure if this is exactly what you had in mind, but could be a possible solution for you.
I had a similar thought to use transpose/cross tab and I've attached an example of what I came up with. I'm not sure if this is how your data is set up, so if you'd like to provide an example of that it would be helpful! but hope this helps:
I've attached what I've come up with. Start by transposing both input tables, grouping by Rank and Rank Name. Then I joined the data based on Rank, Rank Name, and a dynamic Year column. Then multiply and dynamically generate a name (i guessed the naming convention - hoping its right) then cross tab back out. The final select is where i reordered the columns to be grouped together - when you introduce more years you may need to reorder those in there. Im not sure if there is a way to dynamically do that.