Hi there, Happy Thursday! My question is as follows, it would be great if any one of you experts could provide some insights for solving the problem. The following is the original data TesterPreparerReviewer1Reviewer2Reviewer3FTFTSRRJFYSRFTSRRJ FYFTSRRJ RJFTSRRJFY This following is the result that I want to get TesterPreparerReviewer1Reviewer2Reviewer3ResultFTFTSRRJFY1SRFTSRRJ 1FYFTSRRJ 0RJFTSRRJFY1 I want to compare column one's data to column 2, 3, 4, and 5 and then shows the result in a new column (column 6) with the following values 1. Return value 0 if the data in column 1 does not match to any of the columns from column 2-5 2. Return value 1 if the data in column 1 does match to one of the columns from column 2-5 Any of your inputs will be extremely helpful! Thank you in advance. Best, M

How to compare one column to another and return a value to a new column in a data source?

Hi there,

Happy Thursday!

My question is as follows, it would be great if any one of you experts could provide some insights for solving the problem.

The following is the original data

Tester	Preparer	Reviewer1	Reviewer2	Reviewer3
FT	FT	SR	RJ	FY
SR	FT	SR	RJ
FY	FT	SR	RJ
RJ	FT	SR	RJ	FY

This following is the result that I want to get

Tester	Preparer	Reviewer1	Reviewer2	Reviewer3	Result
FT	FT	SR	RJ	FY	1
SR	FT	SR	RJ		1
FY	FT	SR	RJ		0
RJ	FT	SR	RJ	FY	1

I want to compare column one's data to column 2, 3, 4, and 5 and then shows the result in a new column (column 6) with the following values

1. Return value 0 if the data in column 1 does not match to any of the columns from column 2-5

2. Return value 1 if the data in column 1 does match to one of the columns from column 2-5

Any of your inputs will be extremely helpful! Thank you in advance.

Best,

Workflow

Output

Parse

Fuzzy Match

Join

Accepted answers

rarmstrong

The Multi-Row Formula Tool should work

All comments

rarmstrong

The Multi-Row Formula Tool should work

BenMoss

@rarmstrong's solution work but no need to use multi-row if you are only referencing the active row. Standard formula tool will work with the exact formula in the post.

Ben

tom_montpool

Multi-field formula is not required in @rarmstrong's example -- the regular Formula tool should work.

The Contains syntax is fine for your example data, but I'd be a bit careful if the examples are just for illustration -- a Contains query looks anywhere in the string, so Tester "Al" would match with Reviewers Alan, Alfred, and Alice.

I'd be tempted to modify the formula to:

If [Tester]=[Preparer]

or [Tester]=[Reviewer1]

or [Tester]=[Reviewer2]

or [Tester]=[Reviewer3]

Then 1 Else 0 Endif

If you needed something more dynamic, where the list of fields was changing or was fairly extensive, I think you could work with Transposing the data using a Group By on your [Tester] field and then using some Formulas.

turtleth

It worked! Thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703