This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.
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).
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.
@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.