Hello,
See attached snippet of information (same as the attached excel file):
Account # | Description | Sub1 | Sub2 | Sub3 | Sub4 |
1010 | Cash in Bank-Regular | 0 | 0 | 15.53652 | 77.6826 |
1015 | Cash in Bank-General | 0 | 0 | 816.2258 | 4081.129 |
1060 | Petty Cash | 503.02 | 2000 | 805.4814 | 4027.407 |
1070 | A/R-Trade | 3221853.82 | 947190.13 | 984.5023 | 4922.512 |
1071 | Bad Debt Reserve | -6105.58 | -10000 | 793.5245 | 3967.623 |
1074 | Intercompany AR Offset | 0 | -49623.57 | 614.8452 | 3074.226 |
1080 | A/R-Other | 1707.62 | 5411.89 | 640.8 | 3204 |
I am attempting to output Field 1, Field 2 and then Field 3 to Excel file Tab 1.
Next, I'd like to output Field 1, Field 2, and Field 4 to Excel file Tab 2.
Next, I'd like to output Field 1, Field 2, and Field 5 to Excel file Tab 3.
...and so on for over 50 fields.
I am looking for an alternative to running multiple data streams and manually de-selecting the fields in a select tool, and then using an output data tool. I have over 50 fields so am asking whether anyone has a solution that could automate this process. I am thinking possibly a dynamic select tool using a formula but cannot get the formula written correctly.
Any help would be appreciated - Thanks!
Please
Solved! Go to Solution.
At first I over-engineered a response. I didn't actually do the work, but thought about how a macro could call a BATCH macro to repeat the operation many times. Then I thought to myself, "MVP". Minimum Viable Product might be useful. See this pic:
We read the file and TRANSPOSE the data keeping the first two columns plus the NAME, VALUE pairs of the incoming data. You then add a new "header" constant. Next you CROSS TAB the data so you get 3 fields + VALUE.
Now you can output the data to Excel (sorry) and put the "NAME" data to different sheets. The one (1) drawback is that the data on every sheet is called, "DATA".
Check the workflow out with your real data and see if you like it.
Cheers,
Mark
Agreed. I updated the field names before outputting as you recommended. Although each excel tab had an extra column in it, I was able to highlight each tab (in excel) and delete the third column. This solved my problem perfectly. Thanks again.