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,
M
Solved! Go to Solution.
The Multi-Row Formula Tool should work
@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
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.
It worked! Thank you!