Change output column problem... maybe using Control Parameter?

Question

Hey all,

This is a tough one that's been bugging me for weeks. I will try to provide an example as clear as possible. It will be very basic but will hopefully convey what my problem is.

I am currently mapping an Excel document of a Trial Balance (A) into another Excel sheet (B) with their respective rows and account numbers.

It looks like this:

Trial Balance example (A)

Account NumberAccountAmount101Income Tax10102Sales 20103Expenses30104COGS40

Excel sheet (B)

Account Number02/03/202303/03/2023Income Tax101  Sales 102  Total 1Total 100  Expenses201  COGS202  Total 2Total 200

I have set my output column equal to it's account numbers so I can easily perform a Find and Replace to substitute the account numbers with their respective values like so:

(A) before Find and Replace:

Account Number02/03/202302/04/2023Income Tax101101 Sales 102102 Total 1Total 100Total 100 Expenses201201 COGS202202 Total 2Total 200Total 200

(A) after Find and Replace:

Account Number02/03/202302/04/2023Income Tax10110 Sales 10220 Total Total 10030 Expenses20130 COGS20240 Total Total 20070

The two subtotals were calculated separately and replaced afterwards.

So I've set up my entire workflow to have the output column to 02/03/2023. It is a lot more complex than this example.

SO, here comes my question...Is there a way to change my output column to 02/04/2023 in a dynamic way?

I've heard about using a Control Parameter and an action tool but I can't seem to get that to work. I could possibly change my initial output column (02/03/2022) to the word "Value" and then change the value of "Value" but I'm unsure how to do that.

I'm well aware I would have to connect this Control Parameter to each tool needing it which is fine, I just don't know how to set it up.

PLEASE HELP I WILL BE FOREVER IN YOUR DEBT

Samantha_Jayne · Answer

Hey Ben, I'm attaching this here as the example we walked through, but it may be useful if someone else can take it and make it more relevant to support. This control parameter example, enables you to filter the data that goes through the process, by the column name. No need to change the column name to value anymore, but you will have to change your existing workflow to meet the process in the middle. You could also add an interface tool to adjust the file within the macro too. But that is the next stage.

Control Parameter Example Workflow.yxzp

ArtApa · Answer

Hi @BenJones13 - You can change the value of "Value" using a Dynamic Rename tool. Please review the following example: