Here is my problem:
I have 12 different input files, each one corresponding to a month of the year from January - December. Within each of the 12 input files, there is a column ("Column X") for which I I use to add identifiers to all the rows based on the values in that column. The identifying data within Column X can be the same or it can vary slightly across each of my 12 data files.
For example: in January, I need to add the numbers 1 and 2 for my identifier column based on data within Column X - there are 50 rows that say "investor A" and 50 rows that say "investor B". Now say in February, Column X has "investor A", "investor B", and "investor C", so I need to add an additional number 3 in my identifier column for "investor C".
Here is my current solution: I have a template file that tracks the unique identifiers in Column X for each of the 12 files. I have a formula tool for each of my 12 input file streams that adds an identifier column and splices up column X based on the values in that column.
I would like to improve this format because I have 30 data sets (each with 12 input files corresponding to the 12 months of the year). Each data set has column X, but the data within the column varies per data set. For example, another one of my data sets would need to be disaggregated based on "General Z" and "General Y". What I would like is to have one formula tool that changes the data across all 12 of my input files, so that way I could efficiently change the identifying information. At the end of the data stream, these 12 input files are each output into a separate tab (January - December) of the same Excel workbook. Does anyone have any ideas? I am wondering if a union tool would work, however, again I need to output the data onto 12 separate tabs.
