HELP - Outputting 2 constant fields and 1 dynamic field to different excel tabs

[AdamEClark]

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 

[MarqueeCrew · Accepted solution]

@AdamEClark,

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

[MarqueeCrew]

p.S. you can call the "data" AMOUNT. That makes more sense.

[AdamEClark]

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.