Sum columns where names are "unknown" but

[Paul_s_Moody]

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 solution]

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 solution]

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

[Paul_s_Moody]

Thanks for the suggestion, I've tried this approach and it partially works. Combining with the suggestion above gives the solution!

Cleaning up all of the relevant column names to [1997_*], it removes all of the unwanted suffixes and prefixes from the column headers.

I can then use the transpose/aggregate/sum approach from above to generate my total.

I think the advantage of this additional step means that the aggregate formula is much simpler as it only needs one Contains(...) rather tham multiple as before.

The multiple Contains(...) now reside in the dynamic rename step.

[Paul_s_Moody]

Thank you!

This works really well - and combining with the dynamic rename step from below also cleans the suffixes and prefixes from the column names so I have a cleaner data set for the next validation steps.