Select Varying Columns from a single Tab File Read

[LoriDMiller]

I have a situation where I read in a file - it is a single Tab from excel and I read in so the Headers are F1, F2, etc.  I then read from another table the columns I need to get.

Example: Columns K, M and Q.  This Input COULD vary and the column letters could change with each run. It could be K and M or just Q. 

I can get the K, M and Q to be 11, 13 and 17 for field numbers or even to F11, F13 and F17 for file names if needed.  But I can't figure out using any of the Dynamic Tools how to Select the columns that I need.  I would like to pass the columns to the Dynamic select to build the formula but that does not work.  I even tried Append, Union and all sorts of Transpose tools and can't see how to accomplish this.

Short of building a Iterative Macro to then call a dynamic Macro to pull the single column and then reassemble - I am out of ideas.

Any suggestions?

Thank you

[grossal · Accepted solution]

Hi @LoriDMiller,

if I understand it correctly, your setup is like:

F1-F20 in one file and a list of column names in the other file.

I'd rename all F1-F20 columns to letters to match the column names of the other file and than use a Union Tool to combine all. 

The Union Tool allows you to "Output Common Subset of Fields". If you throw both datasets into the Union tool, only those columns will be in the output, that in exist in both datastreams. This basically is your complete "dynamic" part in one tool. 

Would this work for you?

Best

Alex

[LoriDMiller]

YES - Thank you I did not think to change the Union tool to only output the Same Fields.

THANK YOU

[grossal]

You are welcome!

Don't ask how often I tried to find a complicate solution and than one of my colleagues says "why don't you use the Union with Option X" and I am like .......... AAAAAAAAARGH 😄

Have a good day!

[LoriDMiller]

Once I used the Union resulting in Like Columns (love it), I want to ADD those resulting columns.  Since I don't know the names or how many, what tool can I use? As a Side - they currently are WStrings.   I can use tonumber if needed.

This result is perfect.  I want to add the resulting columns whether it be 2,3 or 6. The Summarize tool needs to know the names of columns to SUM BY.  I won't know them.

F11  F13  F17        RESULT DESIRED

10             10                20

          5      5                 10

  2                                  2

What can you suggest to add these Text strings together.

Thank you Again

[grossal · Accepted solution]

This should do the trick:

Output:

The Record ID helps to get them back together and the Transpose will make it possible to sum them up no matter what's the name.

Sample workflow attached.

Best

Alex

[LoriDMiller]

Perfect - I just had to convert them to Numbers first after Transposed - but it worked perfect!  

Thank you for being so available!