Sum Data in Columns by Company - Keep all the Columns

[jportello]

Hi Alteryx Family! Question that I'm having a hard time wrapping my brain around... I know I should be using the transpose and/or the summarize tool but not sure the best way to get the goal that I want. 

I have attached same data and also what I want it to look like. I basically have sent out a questionnaire to lots of companies and will be receiving their responses back in this format. I would like to get the totals by company for each of the columns that have a $ input. 

(the first tab is what I want alteryx to do with the data and the second tab is the example data)

Let me know if theres any other info I can provide! Thank you so much in advance :) 

[jportello]

Sorry was in the longest meeting of my life! lol yes I have attached it now. Thank you again for all of your help - I appreciate it so much!! 

[davidskaife]

Hi @jportello 

Couple of updates, its to do with the data being presented and the fact its including text, and punctuation.

So, change the second Multi-Field Formula to this:

regex_replace([_CurrentField_],"[\,$()]","")

This strips punctuation that you define from the field - which is currently , $ ( )

Just add any other punctuation you wish to remove after the )

You also have text in these fields, so you can tick the 'Letters' option in the Data Cleansing tool to remove them. However, you then have data fields that will be problematic as what should the numerical value actually be:

In the second column you have two groups of numbers as well, which will also cause problems..

For the Dynamic Rename change it to this:

Hope this helps!