Sub columns that repeat with specific values based on the column heading

[Nateebush]

First, I want to apologize if that subject doesn't make sense, as I have been struggling to put into words exactly what it is Im trying to accomplish. I have attached an excel file with where I am at currently in my workflow and the output I am trying to achieve. In my example there are 4 entities (in the actual workflow I believe there are 10)  and each entity has an amount, eliminating amount, adj amount, ending amount, and ending amount without elims. To start, I basically need each of those amounts as a column for each entity. Is it possible to get these columns to repeat? I like the sub column approach, but if it could be accomplished by changing the "Amount" column to be Entity A and then the next 4 amounts and so forth, then by all means that works as well.

If this is possible, then I would want to populate each column with their specific values based on Account information and the Entity name, but that obviously comes second to getting the columns to repeat. Open to other thoughts and ideas as well. Thank you in advance for your help. Really stuck, so figured it wouldn't hurt to ask for help here. 

[wdavis · Accepted solution]

Hi @Nateebush 

Would it work to append the Entity Name to each of the 'Amount' Fields?

In the attached workflow, you firstly pivot your data to get all of the various 'Amount' Fields into one Field, then using a formula tool, amend this column so it now displays as [entityname]_[Amount].

You can then cross-tab the data back to all display within one row of data.

Is this like something you had in mind?

Regards

Will

[Nateebush]

Oh yeah that will play. Thank you so much! I have marked as accepted.