# Compare sentences in 2 cells and retrieve missing values



## Rahu (Dec 15, 2022)

I have 2 cells containing sentences(out of which 1 is right answer and another is wrong). What I want is to find out is the missing words in the wrong answer when compared to right answer. 
Example: Cell A1(Right Answer): *We strongly recommend enabling push notifications on this device.*
Cell B2(Wrong Answer): *strongly enabling notifications device.*

What I want the formula or VBA code to retrieve in cell C3 is  [*We recommend push on this*]. 

Can anyone please help me with this. And also the delimters in the data are spaces only.


----------



## vladimiratanasiu (Jan 5, 2023)

Is this what you want?

Book1ABC1We strongly recommend enabling push notifications on this devicerecommend enabling push notifications on this device 2strongly recommend enabling push notifications on this deviceWe recommend push on this3recommend enabling push notifications on this deviceWe recommend push on thisSheet1Cell FormulasRangeFormulaC1:C3C1=IF(ISERROR(MATCH(B1,A:A,0)),"We recommend push on this","")


----------



## Rahu (Jan 6, 2023)

Thanks for the reply. 
What I am looking for is a code should automatically retrieve missing words when comparing A1 and B1. We should not feed anything like "We recommend push on this". 
A1: aba abb abc abd
B1: aba abb abe abf
C1: abc abd


----------



## Fluff (Jan 6, 2023)

What versions of Xl does this need to work on, you are showing 4 different versions in your profile.


----------



## vladimiratanasiu (Jan 7, 2023)

Rahu said:


> Thanks for the reply.
> What I am looking for is a code should automatically retrieve missing words when comparing A1 and B1. We should not feed anything like "We recommend push on this".
> A1: aba abb abc abd
> B1: aba abb abe abf
> C1: abc abd


See it:
Book1ABC1aba abb abc abdaba abb abe abfabc abd2We strongly recommend enabling push notifications on this device.strongly enabling notifications device.We recommend push on this device.Sheet1Cell FormulasRangeFormulaC1:C2C1=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",IF(ISERROR(SEARCH(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),FIND(" "," "&A1&",",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" ")),MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1),""),""))," "," "))," "," ")Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## Fluff (Jan 7, 2023)

Another option for 365
Fluff.xlsmABC12We strongly recommend enabling push notifications on this device.recommend enabling push notifications on this device.We strongly3We strongly recommend enabling push notifications on this device.strongly enabling notifications device.We recommend push on thisListsCell FormulasRangeFormulaC2:C3C2=LET(a,TEXTSPLIT(A2," "),TEXTJOIN(" ",,FILTER(a,ISNA(MATCH(a,TEXTSPLIT(B2," "),0)))))


----------

