Collapsing similarly named/duplicate columns by summing values

[alpro-23]

Hi all,

I am completely new to Alteryx and I am trying to do a workflow that can collapse all similarly named columns by summing the values within the column. My values currently are dummy variables hence all in 0 / 1. 

Thus far, I can't find any similar posts on Alteryx regarding similarly named columns and summing values. 

Would appreciate help from anyone on this topic!

** In my actual workflow, I have more than 100 columns that faces this issue... hence I chose not to do this manually (ie. typing 'pool' and 'roof') in my workflow. Any way to automate this?**

Input:

Output:

[JosephSerpis]

Hi @alpro-23 I mocked up an approach to tackle this challenge. The approach essentially pivots your data and then uses the text to column tool to split your fields based on _ and then aggregates the data and pivots back to a horizontal format. Let me know what you think?  

[alpro-23]

Thanks Joseph for your quick reply! 

I'm afraid I may have misled the question. My actual columns are not separated by a delimiter ('_'). Instead, they just have a number at the back to differentiate the column names. 

In this case, I'm not sure if a number can be used as a delimiter? 

I'm wondering if a combination of regex and wildcard character would work... But I'm quite new to coding and alteryx so I'm not sure how I can actually do it...

[JosephSerpis · Accepted solution]

I've amended my workflow it uses regex to find the digit at the end of the column name.

[RaviP · Accepted solution]

Hi @alpro-23,

If the column names are having numbers as suffix then you could also this