Simplify the formula

[luo]

Hi everyone,

I am trying to do the reconciliation of two dataset as below

Dataset 1:

Date	No.	A	B	C
20220901	1	a1	b1	c1
20220902	2	a2	b2	c4
20220903	3	a3	b5	c5

Dataset 2:

Date	No.	A	B	C
20220901	1	a1	b1	c1
20220902	2	a2	b2	c2
20220903	3	a3	b3	c3

After join 2 dataset with Date and No., I got the table

Date	No.	A	B	C	Right A	Right B	Right C
20220901	1	a1	b1	c1	a1	b1	c1
20220902	2	a2	b2	c4	a2	b2	c2
20220903	3	a3	b5	c5	a3	b3	c3

I want to compare Column A and Column Right A, Column B and Column Right B, Column C and Column Right C.

The output I want is:

Date	No.	A	B	C	Right A	Right B	Right C	Check
20220901	1	a1	b1	c1	a1	b1	c1	Good
20220902	2	a2	b2	c4	a2	b2	c2	C
20220903	3	a3	b5	c5	a3	b3	c3	B&C

I know I can achieve this using formula IFELSE, but in that case, I will need include all 9 possible situations. Is there a easy way to do that? Thanks

[binuacs]

@luo One way of doing this

[Qiu · Accepted solution]

@luo 
I think this is a dynamic way, can handle any number of situations.
Assume you can have a full join between Dataset 1 and 2. 😁

[luo]

Hi @binuacs, Thanks so much for sharing your thoughts here. I believe your last configuration on the Output Column of formula tool would be Check not Right C. And this kind of complicating the process but thanks!

[binuacs]

@luo you are right, I changed the field name in the workflow, thanks for catching it

[luo]

Thanks @Qiu , I think this probably the best solution. The only problem is if I got a full join. Let me try to filter my data. 

[Qiu]

@luo 
Thank you for the feedback.😁