Dynamically select columns based on the column after it

[nataliad18]

HI!

I truly hope someone can help me and this will make a world of difference for me!

I have a dataset with A LOT of columns, and most of them are not needed. The columns are all named differently, BUT there is one metric that I can choose the columns I need dynamically. It is the column AFTER the column that I need, which is called "check". So essentially, i need to choose ALL columns that are the column before "Check" column (see below example). Is there any way to do this?

Example: Lets say I have 10 columns (with columns name as per example) as below

Column1     Column2     Column3     Column4     Check     Column6     Column7     Column8     Check     Column10   

Is there a way to dynamically choose just Column4 and Column8 (columns before "Check" column)

Thanks a million for any help!
Natalia

[mceleavey · Accepted solution]

Hi @nataliad18 ,

You can simply transpose the data so the names are in a column, then use a multi-row to tag those where "Check" is in the name of the field that comes after.

This then allows you to filter those out and crosstab back:

I hope this helps,

M.

[BS_THE_ANALYST]

@nataliad18 here's one way! 

Before:

After:

Logic: If a column contains the word 'check', it will flag the one before it to pull it out. 

[BS_THE_ANALYST]

@nataliad18; - @mceleavey's solution is better 😂

[nataliad18]

THANK YOU BOTH!!! Both great solutions, @mceleavey  was faster and it worked perfectly so accepted his solution! But I am impressed with both of your skills!!

[mceleavey]