Table comparison using formulas

[sharshra]

I have to compare 2 tables based on complex business rules. I feel it is doable thru dynamic array formula, tried few options, but not getting the required results. Is it too much to do with formula? Any suggestions from experts?

'source' table has the base data. It should be compared with 'spTable' based on the following business rules. Outcome of comparison should be listed in 'compare' column in both tables.

Rules -
1. Compare the 'desc' in 'spTable' for each 'id' with 'desc' & 'id' from 'source' table. Note that 'desc' is in single row in 'source' table & is in multiple rows in 'spTable'.
2. If 'desc' matches between 2 tables, enter 'match' in the compare column in both tables.
3. Check the mismatches between 2 tables. If there is a mismatch, enter 'mismatch' in 'spTable' & 'mismatch-' followed by mismatched element in 'source' table.
a. Mismatch due to items missing from desc - For example, refer id 1. 'source' table has dd,aa,mm. 'spTable' has mm, dd only. Since these match individually between tables, these are marked as 'match' in 'spTable'. But aa is missing. So, it is marked as 'mismatch-aa' in 'source' table.
b. Mismatch due to incorrect items - For example, refer id 5. Number of items are correct, but one of the item is incorrect. 'source' table has m6t but 'spTable' has mt. In the 'spTable' enter mismatch against id 5 & desc mt. In the 'source' table, enter 'incorrect-m6t'.
4. If items from 'source' table are not found in 'spTable', don´t enter any comments in 'source' table.

Conditional format -
As a next step, want to know if it is possible to embed conditional format within formula. If so, how can this be done? I´m anyway doing the conditional format separately. If it can also be combined within formula, it will be a bonus. Otherwise, it is enough if the calculations mentioned above are correctly done.

'source' table:

excel problems.xlsx
	B	C	D
2	id	desc	compare
3	1	dd,aa,mm	mismatch-aa
4	2	bbb
5	3	kk,tt,cc,2e
6	4	p,yu
7	5	ij,gt,fx,w,m6t	mismatch-m6t
8	6	h
9	7	df,ao
10	8	pmk,q,88,un,zt
11	9	ec,bl	match
12	10	72,hp,fv	match
Sheet8

'spTable'

excel problems.xlsx
	F	G	H
2	id	desc	compare
3	9	bl	match
4	9	ec	match
5	1	mm	match
6	1	dd	match
7	5	w	match
8	5	fx	match
9	5	mt	mismatch
10	5	ij	match
11	5	gt	match
12	10	hp	match
13	10	72	match
14	10	fv	match
Sheet8

 

[djclements]

In cell D3, try the following (then copy down):

=IF(
    ISNUMBER(XMATCH(B3,$F$3:$F$14)),
    LET(
        a, TEXTSPLIT(C3,","),
        b, ISNA(XMATCH(a,FILTER(TEXT($G$3:$G$14,"@"),$F$3:$F$14=B3))),
        IF(OR(b),"mismatch-"&TEXTJOIN(",",,IF(b,a,"")),"match")
    ),
    ""
)

In cell H3, try the following (then copy down):

=IF(ISNUMBER(SEARCH(G3,XLOOKUP(F3,$B$3:$B$12,$C$3:$C$12))),"match","mismatch")

You could also use MAP to spill the results for each of the above-mentioned formulas (instead of having to copy down):

=MAP(B3:B12,C3:C12,
    LAMBDA(id,desc,
        IF(
            ISNUMBER(XMATCH(id,F3:F14)),
            LET(
                a, TEXTSPLIT(desc,","),
                b, ISNA(XMATCH(a,FILTER(TEXT(G3:G14,"@"),F3:F14=id))),
                IF(OR(b),"mismatch-"&TEXTJOIN(",",,IF(b,a,"")),"match")
            ),
            ""
        )
    )
)

...and...

=MAP(F3:F14,G3:G14,LAMBDA(id,desc,IF(ISNUMBER(SEARCH(desc,XLOOKUP(id,B3:B12,C3:C12))),"match","mismatch")))

 

[sharshra]

Perfect! Thanks a lot @djclements I wonder how quickly you churn out the solution Great