I was provided 12 separate excel documents that had a consolidating financial for that month; I am trying to organize the data so that one entity's performance performance is shown for each month, before repeating for the next entity.
I consolidated each of the workbooks into one file, and had the filename added as a field (since it lists the month of the financial).
I tried using cross tab and transpose but hit an impasse.
Any suggestions greatly appreciated!
Stan
Solved! Go to Solution.
Hi @sgrabish1
Could you please provide more details (maybe a screenshot of the Cross-Tab Tool configuration) so we can help you better?
Cheers,
Hi @Thableaus,
Thanks for reaching out; I have provided both the source data as well as what I wanted the output to look like. I was able to get Alteryx to consolidate all the workpapers, but didn't know how to finish it out in Alteryx so I exported back into excel, and closed it out.
In the Test Info file, that is what I would like the output to resemble coming from Alteryx. I know it is a cross-tab exercise but could not figure out how to do since there were multiple entities.
Appreciate any help here!
Thanks.
Stan
Hi @sgrabish1
The data file that you provided doesn't contain enough information for us to be able to figure out the algorythm. All we can see from this is that column F and G for April from your input end up in month 4 in the output. F & G for December end up in month 12 in the output.
Questions
1. The entity names change from April to Dec, DEF->SEC, ABC->SCR. Is this only because you manually changed the first row in the sample? Do we assume that Col G is always ABC for the entire file, or should we match entity names for each month.
2. What about the other Entities, JO, ST, SHH, HPT, MRS, SC? do you want the monthly breakdown for these as well?
Dan
@danilang Thanks for reaching out.
I meant to change the second row to the same as 1st row with ABC and DEF; I also only showed April and Dec data as wanted to keep file minimal. done in haste; sorry about that.
I figured I just needed to see the proposed workflow and that it would be applicable to all entities and months, so I only showed a subset of the data.
Thanks for follow up!
Hi @sgrabish1
Here's a solution
Once the null get removed, a multi-row formula builds the final sort order to retain the order of categories. Then it selects all the relevant entity columns and builds the year, month and month numbers. The transpose-crosstab pair flips the values from Entity X Month to to Month X Entity. Then there's a sort and clean up to give you
Note that the results only have values for April and December but does include all the Entities listed. As you add in more monthly data, the rest of the month columns will fill out. I also changed dummy names that you put in back to the original ones in your input file
Dan
Thank you!
Stan