Sum Column Based on text contained in title row

[mabdulh1]

HI, 

So the issue I am trying to resolove is the following: 

I have a standard column titles for different excel files every year and I want to sum all the rows below this title. The issue arises where they may not be in the same order every year or maybe one year have zero values so the column is not added. How can adjust my workflow as it currently assumes the setup of excel file is standardized.

Below is the workflow but essentially I am using the summarize tool but it only does what i need on the test data I used originally. I had thought the info would be in a standardized format so didnt think to do anything otherwise. I was thinking to add a IF statement to look for the column and sum amounts below it whether there was 15 or 150 values. Not sure if this is the best approach or there is an easier one to map.

[fmvizcaino]

Hi @mabdulh1 ,

I attached a workflow with a solution I thought for your problem.

If I understood well what you need, one way of making it happen is to use a text input as a standard structure that will create all the columns your excel should have but didn't. With that, you will guarantee that your summarize tool will not give you an error anymore.

Let me know if that works for you.

Best,

Fernando V.

[mabdulh1]

To clarify based on the workflow you created if this year the columns range from A-F. Let say you have a table like the one below and I want to sum columns E, G, H, I. If the columns aren't always created in alphabetical order every but I always want to sum E,G,H,I whether they are the 5th,6th-8th columns or the 1st 4 columns. So I am trying to figure out the best way to say look for column E and Sum all the rows below that column, look for column G and sum all the rows below and so on. I am attempting to have the data come in standardized format so that my current workflow always works but I thought if I could build it so the odd times where the data doesnt come in that format it wont matter because alteryx will automatically adjust.

[mabdulh1]

Can you explain your workflow a bit more maybe if you can provide a little more detail. After my response I think I see what you mean I could do but Im not too sure if i get it completly.

Thanks for your help!!

[fmvizcaino · Accepted solution]

Hi @mabdulh1 ,

Your excel files don't need to be all standardized and in alphabetical order. The union tool concatenates files based in the column names so it doesn't matter the position of your column as long as you know the column name you want to use.

The only thing my workflow is doing is to get every sheet you have in your excel file and concatenating everything considering the column names. One thing you need to be careful in this workflow is that every sheet needs to have its column names in the same row number.

Hope that I answered your question.

Best,

Fernando V.