select columns based on value in a row

[mchitlur]

I am trying to select columns based on Value in “Month” column. See Example below. If “Month” has OCT it should pick all columns Jan thru Sep and column “New OCT” (Highlighted in Blue). The Month Column is based on a formula and it changes. So if it’s NOV, It should pick all months Jan thru OCT and “New Nov”. See screen shot below

[apathetichell]

This is doable. You'd need a batch macro. you'd need to figure out if you are using YTD or an aboslute value (ie January 2025 is greater than December 2024)

You'd throw your main datastream in a batch macro (macro input) --- you'd configure a control paramater

you'd use dynamic select -- and a formula like:

!(regex_match(replace([Name],"New_",""),"\w{3}")) OR (tonumber(
datetimeformat(datetimeparse(replace([Name],"New_",""),"%b"),"%m"))<=9)

note ---- you will need to build out logic to kepe any other fields that you want (or to drop any other fields that you want)--- currently this would keep all fields with greater than 3 letter and not prefaced with New_ ---> for those fields it checks against the number 9--- don't worry this is a temp number.

you will then use a macro output after this. you will use the control parameter -> action tool -> replace a specific string - and replace the 9 in your dynamic select.

you would use interface designer to allow for union by name and save this.

in your main workflow you would  attacht the main workflow to your macro input. you use a summarize tool on month in group by mode -> then use tonumber(datetimeformat([month],"%b")) to get your value of month into a single numeric value. this hooks into your batch macro via the control parameter and replaces the 9 at run time.

[CoG]

Here is a non-macro solution that can achieve this outcome as well:

By using the Field Info Tool, we can manipulate the column names and identify which ones to keep/remove via Dynamic Select.

Hope this helps and Happy Solving!