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

Qiu

@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. 😁

0922-luo.yxmd