Sum Column Based on text contained in title row

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.

Datasets

Developer Tools

Data Investigation

Accepted answers

fmvizcaino

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.

All comments

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.

Sum Column Based on text contained in title row.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703