Dynamically select columns based on the column after it

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

Input

Preparation

Accepted answers

mceleavey

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,

select check.yxmd

All comments

mceleavey

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,

select check.yxmd

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.

dynamic_removing_columns.yxmd

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

no problem.gif

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703