Find a particular column and select all columns before it

[Mtvbqb]

Hello,

I have a data set where the number of columns will shift. I know that I want to keep column "GROSS" and all of the columns before it.

I'm looking into the Dynamic Select tool, and have been able to select the "GROSS" column.

FINDSTRING([Name], "GROSS")>-1

I'm at a loss on how to select the columns before it as well. Could I somehow determine the FieldNumber of the "GROSS" column and then do a select FieldNumber

[grossal]

Hi @Mtvbqb,

I am not sure if it's the best way, but I'd do it differently.

Steps:

1) Shift Columns to rows (Transpose Tool)

2) Multi-Row-Formula to determine all values before 'Gross' and adding a 'Keep' Column.

3) Filtering all unneeded columns (Filter Tool)

4) Shifting everything back (Cross Tab)

I have tried it with two different examples and it worked well. I'll attach my workflow for you. Let me know if it helps.

Best 

Alex

[Mtvbqb]

Hey @grossal

Thank you so much for the suggestion! Unfortunately, I gave it a shot and didn't produce the expected results.

The data isn't correctly retained after either the transpose or the transform.

[grossal]

Ahh, I see my error.

I have adapted the workflow. I am struggling a bit with shifting the columns back tho.

I'll try to figure it out while getting some food.

Current version is attached.

Alex

[Mtvbqb]

Apologies for the delay, I had to mock up some data. The attached is representative of the actual data.

[grossal · Accepted solution]

I think I finally got it. I tried another approach this time. I should have gone with your first guess (Dynamic Select) in the beginning and we would have been quicker.

Workflow attached.

Alex

[Mtvbqb]

Dude,

That totally worked!!! Thank you so much!

One tiny edit, the formula on the Multi-Row Formula tool was slightly off

IF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')
THEN 'Remove'
ELSE 'Keep'
ENDIF

Since it was looking for 'No' but the formula was set to output 'Remove'. I simply changed the 'Remove' to 'No' and it worked like a charm!

IF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')
THEN 'No'
ELSE 'Keep'
ENDIF

[grossal]

I am glad we finally figured it out!

Ohh, looks like I forgot to change that. I'll edit my post and attach a fixed version in case someone will find this post in the future.

[jones-b]

Thanks for going that extra mile @grossal.  I found this today and it let me to solve my problem!  I actually had something slightly different though.  I needed to split my columns into thirds but with each third keep the very first column.  The names of the columns that identify where I need to split are consistent BUT I don't know how many columns will be between them.

for example, I have columns: beginning, a, b, c, first divider, d, e, f, g, second divider, h, i, j, k, l

I needed to split these into three sets

1:3 - [beginning] through [d]

2:3 - [a] AND [first divider] through [h]

3:3 - [a] AND [second divider] through the end

BUT the number of columns between [beginning], [first divider], and [second divider] can change each time 

[shobhit_gupta]

@grossal Hello, can we do something similar where I should be able to keep only N number of columns before/after a specified column (here 'GROSS'). Thanks in advance.