Run a macro on several columns at the same time

[gdarya]

Hello, I am looking to run a macro that would update several columns at the same time. My goal is to replace null values in one field with another field that has the same name but starts with "Right_" (common columns after a join, but one has more nulls that the other). I cannot use a multi-field formula since I cannot refer to the other column as "the same but starts with Right_". I don't want to use transpose either because I'm working with large data.

I've created a simple macro that does what I want for a single selected column. How do I make it work on a list of columns?

[MattBSlalom · Accepted solution]

The Dynamic Replace tool has the magic you want.

In this sample, I filter to just the first row to Transpose to get the Header values, then filter out those with the "Right_" keyword.  A formula is added to generate the Expression fields needed for each of the Header values.  Finally the Dynamic Replace executes those Expressions to generate the desired output.

Sample workflow:

[gdarya]

Great - thank you!