Same Formula with Multiple Field Inputs

[kfish]

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!

[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.

[ShankerV · Accepted solution]

Hi @kfish 

One way of doing this.

Many thanks

Shanker V

[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!

[ShankerV]

Hi @kfish 

Could you please share more information to analyze please.

Can you compare on my workflow and your applied workflow and let me know in which step you see a difference.

Many thanks

Shanker V 

[kfish]

Hi @ShankerV I ended up figuring it out! Im running this on around 400 fields and one of the Day 1 fields was missing their Day 0 fields so pulled it in and it fixed the issue. Thank you!