Selecting the columns coming after a specific value in a cell

[Gandalf_NotGrey]

Hi

I have a table where I have to select the columns coming after the text "Set B". This can be present in any column, and either in row 4, 5 or 6.

See image 2. In field F17, row 5, we have "Set B" written, my requirement is to select all the columns starting from F17, which is where "Set B" is written. F17 to F33.

Similarly, in image 3, the text "Set B" is in field F8 and in 4th row, so all the columns on and after F8 are needed. F8 to F33.

Is there any way to do this dynamically?? I have attached a sample file as well.

Also, sometimes the data, which is there in the excel file, comes in e notation when pulled into Alteryx, I don't want that to happen and get the data only in decimals without the e notation.

[binuacs]

@Gandalf_NotGrey If your column SET B is always in the row 4 in any of your input file then the attached workflow should work

[goatley1]

I'm assuming you won't have to clean up the data as much as I did so the first filter will have to be customized. Essentially you need to ensure that Set B is the first record. Sample the first record. Then we transpose the fields and test for the Set B in the value field.  Use a multi row formula to find Set B and flag every record from set B while the field name is <= F33 using if then (I use 1 and 0).  Drop all the records that don't pass the multi-row formula with a 1.  Cross tab your field names back to columns.  Then drop all records with the sampling tool.  Union with the original filtered data set and keep only common fields.  This should work for your situations.  

Now I followed your instructions to a T this will always return the first field Fn (where n = any number form 1-33) through F33.  If you meant to grab the next n fields, the you will have to tweak the multi-row formula to accumulate instead of flag.  Subsequently filter for binary_test <= n.  Hope this Helps

[goatley1]

This will only work for record 3 thus not dynamic.

[Gandalf_NotGrey]

Thank You so much @binuacs for the response.

It would be very helpful if you can explain the purpose of the Dynamic Select Macro, so that I can troubleshoot the error I am getting.

I am getting the below error (image attached), while trying to replicate the solution you have suggested. Please see the attached image.

Tool 2 is the Dynamic Select Tool and for some reason it is saying that Output 3 is not invalid. 

Thanks

[Gandalf_NotGrey]

And yes @goatley1 I am okay with that value in record 3 since that part I am able to make constant.

[goatley1 · Accepted solution]

I'm sorry, I thought I had uploaded a solution I drafted. I'm not sure what that specific error is without more detailed context.  This workflow should get the job done for any input.  You may need to adjust the filter, because I'm assuming the data you have comes out slightly different than what is provided.  In my workflow you simply filter to make the record with Set_B the first record. everything else should work like clockwork.  No need for pesky macros.

[binuacs]

@Gandalf_NotGrey make sure that the summarize tool produces the correct formula similar to the given workflow

it should be like [name] = 'Field Name' OR [name] = 'Field Name', etc, if you are still getting the error, post the screenshot of your summarise tool output

[flying008]

Hi, @Gandalf_NotGrey  & @binuacs 

There is not need macro for this case. (  'Set B' always in the record 3 as you assign.)

[Gandalf_NotGrey]

Hello @flying008 Can you please attach your workflow.