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.
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.
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.
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.
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?
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