Selecting the columns coming after a specific value in a cell

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.

2.png

3.png

Sample Alteryx File.xlsx

Workflow

Datasets

Developer

Parse

Common Use Cases

Help

Dynamic Processing

Preparation

Accepted answers

goatley1

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.

example_20250425.yxmd

All comments

binu_acs

@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

Set_25042025.yxzp

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

example_20250425.yxmd

goatley1

This will only work for record 3 thus not dynamic.

Gandalf_NotGrey

Thank You so much @binu_acs 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

Dynamic Select Macro Errors.png

Gandalf_NotGrey

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

goatley1

example_20250425.yxmd

binu_acs

@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 & @binu_acs

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

录制_2025_04_26_10_03_47_777.gif

Gandalf_NotGrey

Hello @flying008 Can you please attach your workflow.

flying008

Hi, @Gandalf_NotGrey

Due to security policy restrictions, I am unable to upload. yx * files. Therefore, please refer to the annotations in the animation for all settings or formulas.

AncientPandaman

@flying008 did it still restriction on if you open the workflow in notepad and paste the text here. 🤣

Gandalf_NotGrey

Hello @flying008 Can you please share the configuration screenshots for the Multi-Row Formula, Dynamic Rename and Dynamic Select tools.

goatley1

dummy field.png sample tool 2.png sample tool.png

Gandalf_NotGrey

Thank You so much @goatley1 @flying008 @binu_acs for your responses. This was very helpful.

I was able to apply @goatley1 's solution and it worked perfectly.

@binu_acs - I was not able to resolve the error that was coming and due to security issues, I could not share the exact workflow. I really liked your approach though.

@flying008 - I could not see the configuration screens of the Multi-Row Formula, Dynamic Rename and Dynamic Select tools, so I was not able to try your solution. Appreciate your kind efforts for this.

Thank you all for your assistance on this.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703