Hi, I need to run a very basic If Then statement on multiple fields in my data. At first I was thinking I could use the Multi-Formula tool, but since it has multiple field inputs not sure that it would work. Essentially, I have a dataset where I unioned two datasets of the same fields but with different as of dates and I need to compare each of those fields to each other. For Ex my dataset looks like: Day 0_CodeDay 1_CodeDay 0_RateDay 1_RateAA11.5BC22 And I need to run this formula: IF Day 0_"Field" = Day 1_"Field" THEN 'Y' ELSE 'N" ENDIF that will turn it into this: Day 0_CodeDay 1_CodeMatch?Day 0_RateDay 1_RateMatch?AAY11.5NBCN22Y My problem is that I have around 400 fields total that this needs to be ran on so doing it manually would take a long time. I don't think the Multi-formula tool will work since from what I understand it only runs on one field at a time rather than two. Would a macro possibly work for this? Thank you!

Same Formula with Multiple Field Inputs

Hi,

I need to run a very basic If Then statement on multiple fields in my data. At first I was thinking I could use the Multi-Formula tool, but since it has multiple field inputs not sure that it would work.

Essentially, I have a dataset where I unioned two datasets of the same fields but with different as of dates and I need to compare each of those fields to each other. For Ex my dataset looks like:

Day 0_Code	Day 1_Code	Day 0_Rate	Day 1_Rate
A	A	1	1.5
B	C	2	2

And I need to run this formula: IF Day 0_"Field" = Day 1_"Field" THEN 'Y' ELSE 'N" ENDIF that will turn it into this:

Day 0_Code	Day 1_Code	Match?	Day 0_Rate	Day 1_Rate	Match?
A	A	Y	1	1.5	N
B	C	N	2	2	Y

My problem is that I have around 400 fields total that this needs to be ran on so doing it manually would take a long time.

I don't think the Multi-formula tool will work since from what I understand it only runs on one field at a time rather than two. Would a macro possibly work for this?

Thank you!

Workflow

Regex

Macros

Iterative Macro

Accepted answers

ShankerV

Hi @kfish

One way of doing this.

Many thanks

Shanker V

Same Formula with Multiple Field Inputs.yxmd

All comments

ShankerV

Hi @kfish

One way is to Transpose the data and apply the logic.

Many thanks

Shanker V

kfish

Thanks for the response! How does that work if i need a new field created for every "Match?" scenario?

Christina_H

Here's a way to do it for your data. It takes a couple of rounds of transpose/crosstab. You might be able to simplify that by combining the two datasets differently earlier in your process.

I adjusted your column names so they would sort correctly - cross-tabbed fields sort alphabetically.

Match columns.yxmd

ShankerV

Hi @kfish

One way of doing this.

Many thanks

Shanker V

Same Formula with Multiple Field Inputs.yxmd

ShankerV

Hi @kfish

Checked with more input fields, the workflow will work dynamically.

For your information as @Christina_H and my solution does not have the column name as Match? because we cant have more than 1 column with the same column name.

Hence something to be added before/after the column name to make the column name unique.

Please let me know if you have any more questions.

Many thanks

Shanker V

kfish

Thanks!! This is so close to working, but for some reason when it transposes back to the original format, I lose match data on all the same rows. Do you know why that would happen?

Sorry for my horrible shading, just is data that i can't share!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703