Sum columns where names are "unknown" but

Question

I'm trying to set up a workflow to validate data, so need to sum columns, however not all of the column names are known in advance.

However they will all contain identifiable text within them, e.g I want to create a summary column called"Pre_97_Total" where this is the sum of any existing columns containing "Pre_97", "Pre 97", "Pre 1997" or "Pre_1997" within them.

The actual column in the data set contain prefixes and suffxes, but all have the "key" text as part of the name.

In Excel/VBA  I'd add an additonal header row tp the data with a boolean based on a messy embedded IF(...) formula to get the True/False for the if a column header contains the relevant text, and then use a SUMIFS(...) based on the True/False in this new header column.

The validation is that is the customer joined after 1998, then the sum of all of the Pre 97 columns should be zero!

Is there a "clean" way to do this?

I've tried using a dynamic select to get the relevant columns and then a summation, but I lose the customer ID and join date (and all other columns) so have to somehow join the data back togther and as I've lost the ID in the selected data I can't see a way to join!

Thanks

Christina_H · Accepted Answer

To do this, I would transpose your data, identify and sum the rows of interest, then join back onto the original data.  Here's an example with some made up data.

amanda_payne · Accepted Answer

Have you thought about using the dynamic rename tool? An IF statement may get you what you want in the tool.

Edit to add: Dynamic Rename Tool | Alteryx Help