Formula to pull the value from a column based on the value of another column

[rybog98]

Hello,

I have a data set with over 100 columns. In the data set I have a column called "COLUMN_NAME" that contains the name of one of the other columns. What I need to do is pull the value from the column that is specified in "COLUMN_NAME". Any ideas?

Thanks in advance

Here is an example of what I am trying to do:

COLUMN_NAME	VAR_1	VAR_2	VAR_3	Target Output
VAR_1	10	8	6	10
VAR_3	1	2	3	3
VAR_2	5	7	9	7

[atcodedog05]

Hi @rybog98 

Here is how you can do it.

Workflow:

1. Use tranpose to convert columns to row.

2. Filter row where column name = name (actual column name)

3. Using find and replace vlookup on record id to join the value back to main data. You can use join tool instead too.

Hope this helps : )

[Emil_Kos]

Hi @rybog98,

I have prepared a solution for you:

The output:

[rybog98]

Thanks for the quick response @atcodedog05 and @Emil_Kos. I have a few concerns with these solutions:

1. My data set can be very big (Millions of records) and I have found transpose and cross tab can be very time consuming

2. I actually need to return 2 columns. One is "COLUMN_NAME"_IN and "COLUMN_NAME". I couldn't figure out how that would work with the transpose method without using a lot of tools and joins

COLUMN_NAME	VAR_1_IN	VAR_2_IN	VAR_1	VAR_2	VALUE_IN	VALUE_OUT
VAR_1	10	8	6	4	10	6
VAR_2	1	2	3	4	2	4
VAR_2	5	7	9	11	7	11

[rybog98]

COLUMN_NAME	VAR_1_IN	VAR_2_IN	VAR_1	VAR_2	VALUE_IN	VALUE_OUT
VAR_1	10	8	6	4	10	6
VAR_2	1	2	3	4	2	4
VAR_2	5	7	9	11	7	11

[rybog98]

This is what I have came up with but still think there should be an easier way:

[atcodedog05]

Hi @rybog98 

Here is how you can do it.

Hope this helps : )

[atcodedog05]

Hi @rybog98 

My approach is similar around yours. I think most of the possible approaches will be around same lines.

Hope this helps : )

[Emil_Kos]

Hi @rybog98,

Katie Howard from Alteryx shared with me a tip on how you can handle it. 

You can refer to this post. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Formula-based-on-the-cell-defi...

In crew macro, there is a dynamic formula. I just opened it and it doesn't have any transpose inside so it might be faster on a big data set.

You’ll need to download and install the CREW Macros first (this requires the dynamic formula tool to work).  I’ve attached the macro to the package above, but I’d recommend installing it first. Be sure to unzip the contents before you run the installation file.

[atcodedog05]

Hi @Emil_Kos ,

As per my experience with the Crew dynamic formula, it can only be applied on a column level not at a cell level. Its similar to dynamic replace tool.

Again I might be wrong can you provide an example with a cell level formula applied using Crew dynamic formula tool. I am interested to learn more on the scenario 🙂

[Emil_Kos]

Hi @atcodedog05,

The truth is I never used it and I am not able to focus on it now but thank you for letting me know 😀

@rybog98 If I will find something else that might be helpful for your I will let you know.