Select Columns based on Formula?

[kpmg_lc_analyst]

I need to automatically select and perform operations on columns where the first record contains certain values.  For example, if you refer to the picture below, I need to automatically select the columns where the first record contains the "/".  Here, that's F2, F3, and F4. Other times, it could be different.  How would I go about doing this? 

[JohnJPS · Accepted solution]

I would apply a RecordID, Transpose, apply the Formulaic Filter, then CrossTab back and Union to original table using auto-Config by Name and including only a common subset of fields.  An example of this is attached.

[kpmg_lc_analyst]

isn't there any easier way to do this? 

[JohnJPS]

I can't think of an easier way off the top of my head, however this could probably be turned into a macro in order to simplify it for different usuages, (e.g. just provide the Formula and let the macro handle everything else).

[JohnJPS]

Along those lines, here is such a macro (attached).  The down-side, is you don't have the Formula-building apparatus for the macro's input textbox, so it will be trial and error getting the formula correct.

[patrick_digan]

@kpmg_lc_analyst Assuming the field names can change (or you would have to add another dynamic rename at the end with the field names you want), a dynamic select and dynamic rename may be a solution for you.