Table comparison using formulas

sharshra

Active Member
Joined
Mar 20, 2013
Messages
404
Office Version
  1. 365
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
BCD
2iddesccompare
31dd,aa,mmmismatch-aa
42bbb
53kk,tt,cc,2e
64p,yu
75ij,gt,fx,w,m6tmismatch-m6t
86h
97df,ao
108pmk,q,88,un,zt
119ec,blmatch
121072,hp,fvmatch
Sheet8


'spTable'
excel problems.xlsx
FGH
2iddesccompare
39blmatch
49ecmatch
51mmmatch
61ddmatch
75wmatch
85fxmatch
95mtmismatch
105ijmatch
115gtmatch
1210hpmatch
131072match
1410fvmatch
Sheet8
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
In cell D3, try the following (then copy down):

Excel Formula:
=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):

Excel Formula:
=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):

Excel Formula:
=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...

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

Forum statistics

Threads
1,223,880
Messages
6,175,153
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top