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

Hi @rybog98,

I think this will work for you.

This is a combination of batch macro + macro from the crew macro:

Just a reminder how to get a crew macro:

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.

Big kudos to @khess85 as she created this solution!

ForEmil_Community_v2.yxzp

All comments

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 : )

column name.yxmd

Hi @rybog98,

I have prepared a solution for you:

The output:

Transorm the data.yxmd

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

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

Hi @rybog98

Here is how you can do it.

Hope this helps : )

column name.yxmd

Hi @rybog98

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

Hope this helps : )

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Formula-based-on-the-cell-definition/td-p/479842

Hi @rybog98,

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

You can refer to this post.

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.

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 🙂

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.

Hi @rybog98,

I think this will work for you.

This is a combination of batch macro + macro from the crew macro:

Just a reminder how to get a crew macro:

Big kudos to @khess85 as she created this solution!

ForEmil_Community_v2.yxzp

patrick_digan

@rybog98 I have an idea for an eval function which would be useful in a case like yours if Alteryx implemented it. It would then be a formula like:

Eval("["+[Column_Name]+"]")

@patrick_diganThis is what I was hoping for. I think this would be very useful for many things.

@Emil_KosWe ended up doing something similar. We used a batch macro to pass the formulas to a formula tool. We decided this was the best approach given our use case and data constraints. Thanks for your help!

Hi @Emil_Kos

I am bookmarking your post with batch macro approach 🙂

patrick_digan

For kicks and giggles, you can use @jdunkerley79 's alteryx abacus to store the data behind the scenes in a weird alternate universe and then use magic to bring the data back. You have to have a good understanding of Alteryx's engine to understand the VarNum functions, but they're powerful once you corral them. They're also a third party install, which is a major thing that keeps more people from being able to use it.

column name.yxmd