Selecting data in fields where field names are in a column

Hi all,

I have a workflow which uses a raw data set of about 1000 columns. I have one column where each record has one of the other column names in that column. I want to be able to build a formula that pulls through the data from the respective column where the record has that column name, named. Is this something quite easy to do? I don't want to have to build 150 different formula tools.

Example Data attached.

Example Data.xlsx

Developer

Accepted answers

Hi @jamesfbeard1,

here is an easy way to do this:

Output:

What happens:

1) Add a RecordID

2) Shift all the data columns

3) Join everything based on the column names and RecordID back together

4) Sort it

I'll attach the workflow for you. Let me know what you think.

Best

Alex

2020-04-19-Selecting data in fields where field names are in a column.yxmd

All comments

Hi @jamesfbeard1,

here is an easy way to do this:

Output:

What happens:

1) Add a RecordID

2) Shift all the data columns

3) Join everything based on the column names and RecordID back together

4) Sort it

I'll attach the workflow for you. Let me know what you think.

Best

Alex

2020-04-19-Selecting data in fields where field names are in a column.yxmd

Hi @jamesfbeard1 I mocked up a workflow let me know what you think?

Columns_19042020.yxzp

Hi @jamesfbeard1 ,

Here is an approach to accomplish your requirement.

All columns after from book_name is converted to rows so that if can matched to find the new value.

Only column Names that matched Column (with Column Names) were filter for use.

Later the old table with data and new table with generated value is joined over Name (which is a unique key in the scenario).

Generated output looks like this with the column outputted value having the new value.

The workflow is attached in the reply.

I hope this helps your purpose. If there any help you need with anything specific you are looking for please reply back.

If it solves your use-case please like the reply and mark the reply as accept solution.

PS: really appreciate that you were able to provide the file or else it would have been harder to provide solution.

Thank you for sharing this scenario. It was a great exposure.

Cheers and Happy Analyzing : )

This worked perfectly thanks!

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703