# Formula to compare two cells



## PTR2022 (Dec 31, 2022)

Hi!

Is there any formula or macro to be able to compare two cells in excel and return true or false but with a margin of error? That with a single word can tell me that it is true. 

For example


BRANDBRAND 2STATUSCONDESACONDESA SPARKLINGTRUE​

The status is true because brand 2 contains the word "CONDESA".

Thanks!


----------



## Flashbond (Dec 31, 2022)

```
=IFERROR(IF(SEARCH(A2,B2),TRUE),IF(SEARCH(B2,A2),TRUE))
```


----------



## Phuoc (Dec 31, 2022)

Try this

Book1ABC1CONDESACONDESA SPARKLINGTRUE2CONDESA SPARKLINGCONDESATRUE3aaabbbFALSESheet1Cell FormulasRangeFormulaC1:C3C1=COUNT(SEARCH(A1,B1),SEARCH(B1,A1))>0


----------



## Peter_SSs (Jan 1, 2023)

Welcome to the MrExcel board!



PTR2022 said:


> The status is true because brand 2 contains the *word *"CONDESA".


If you are actually looking for *whole words* and *not parts of words* then you may need some more.

23 01 01.xlsmABC1BRANDBRAND 2STATUS2CONDESACONDESA SPARKLINGTRUE3CONDESAABCONDESARA FLATFALSE4CONDESA FLATFLATTRUECompareCell FormulasRangeFormulaC2:C4C2=LET(a," "&A2&" ",b," "&B2&" ",COUNT(SEARCH(a,b),SEARCH(b,a))>0)

BTW, is it possible that both columns could have multiple words and you want TRUE if any word matches?
If so, possibly this instead?

23 01 01.xlsmABC6BRANDBRAND 2STATUS7CONDESA SPARKLINGNON-SPARKLING CONDESATRUE8CONDESA SPARKLINGNON-SPARKLING CAVAFALSECompareCell FormulasRangeFormulaC7:C8C7=COUNT(SEARCH(" "&TEXTSPLIT(A7," ")&" "," "&B7&" "),SEARCH(" "&TEXTSPLIT(B7," ")&" "," "&A7&" "))>0


----------



## PTR2022 (Jan 2, 2023)

Peter_SSs said:


> Welcome to the MrExcel board!
> 
> 
> If you are actually looking for *whole words* and *not parts of words* then you may need some more.
> ...


Hi Peter thanks for your answer, you are absolutely right, I was looking for a formula where I can compare text in both cells and have it mark as true if it matches even if it has only one word.

However, I put the formula that you shared with me, I get an error, could you please help me?

Thanks!

Book1 (version 1).xlsbABC1BRAND 1BRAND 22AMAREN (ROOM ALEX CANAS)WINE AMAREN BOT 750 ML FRANCEFALSESheet4Cell FormulasRangeFormulaC2C2=COUNT(SEARCH(" "&TEXTSPLIT(A2," ")&" "," "&B2&" "),SEARCH(" "&TEXTSPLIT(B2," ")&" "," "&A2&" "))>0


----------



## Peter_SSs (Jan 2, 2023)

PTR2022 said:


> However, I put the formula that you shared with me, I get an error, could you please help me?


When I copy your mini sheet to my workbook, the formula returns TRUE

Try starting a fresh worksheet and copy the the mini sheet from post #5 using this icon at the top left




then select cell A1 in your fresh sheet and Paste

If C2 then returns TRUE it most likely means that the spaces in the original data are not 'normal' spaces.
In that case, in blank cells in the original sheet that is returning FALSE put these two formulas and report the results.


```
=CODE(MID(A2,7,1))
```


```
=CODE(MID(B2,5,1))
```


----------



## PTR2022 (Jan 2, 2023)

Peter_SSs said:


> When I copy your mini sheet to my workbook, the formula returns TRUE
> 
> Try starting a fresh worksheet and copy the the mini sheet from post #5 using this icon at the top left
> View attachment 81905
> ...



Hi Peter, thank you for your reply.

Unfortunately the formula continues to give false, the results are as follows:

BRANDS.xlsxABCDE1BRAND 1BRAND 22AMAREN (ROOM ALEX CANAS)WINE AMAREN BOT 750 ML FRANCEFALSE3232COMPARECell FormulasRangeFormulaC2C2=COUNT(SEARCH(" "&TEXTSPLIT(A2," ")&" "," "&B2&" "),SEARCH(" "&TEXTSPLIT(B2," ")&" "," "&A2&" "))>0D2D2=CODE(MID(A2,7,1))E2E2=CODE(MID(B2,5,1))

All the best


----------



## Peter_SSs (Jan 2, 2023)

PTR2022 said:


> Unfortunately the formula continues to give false


Two more things to check ..


Do you actually have the TEXTSPLIT function? In a blank cell start typing =TEXT and check if this pop-up appears of if TEXTSPLIT (& some other functions) are missing






If you do have TEXTSPLIT then better check the space on the other side of AMAREN in B2 with 

```
=CODE(MID(B2,12,1))
```


----------



## PTR2022 (Jan 3, 2023)

Peter_SSs said:


> Two more things to check ..
> 
> 
> Do you actually have the TEXTSPLIT function? In a blank cell start typing =TEXT and check if this pop-up appears of if TEXTSPLIT (& some other functions) are missing
> ...


Hi Peter, you are right, the formula doesn't work because I don't have the TEXTSPLIT function. It seems to me that the only formula to fix it is to update my office, however, the computer is company owned and I don't have permissions to update or install programs. I will have to give up. 

All the best


----------



## Peter_SSs (Jan 3, 2023)

PTR2022 said:


> Hi Peter, you are right, the formula doesn't work because I don't have the TEXTSPLIT function.


Thanks for confirming.



PTR2022 said:


> I will have to give up.


Don't do that so easily. 
You could try this instead.

23 01 01.xlsmABC1BRAND 1BRAND 22AMAREN (ROOM ALEX CANAS)WINE AMAREN BOT 750 ML FRANCETRUE3OTHER (ROOM ALEX CANAS)ROOM BOT 750 ML FRANCEFALSE4CIDER CAN 750 MLSWINE AMAREN BOT 750 ML FRANCETRUECompare (2)Cell FormulasRangeFormulaC2:C4C2=COUNT(SEARCH(" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),SEQUENCE(9,,,100),100))&" "," "&B2&" "),SEARCH(" "&TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),SEQUENCE(9,,,100),100))&" "," "&A2&" "))>0

Note though that dealing with words in Excel is not always easy. For example, in row 3 below *we* can see that both contain the word 'ROOM' but the formula returns false because may formula tells it to determine words by looking for spaces (or beginning/end of the text) to mark where words begin/end.
Also, row 4 returns TRUE because "750" is common to both even though *we *may not consider that a word.

If this is still not sufficient, are you allowed to use macros?


----------



## PTR2022 (Dec 31, 2022)

Hi!

Is there any formula or macro to be able to compare two cells in excel and return true or false but with a margin of error? That with a single word can tell me that it is true. 

For example


BRANDBRAND 2STATUSCONDESACONDESA SPARKLINGTRUE​

The status is true because brand 2 contains the word "CONDESA".

Thanks!


----------



## PTR2022 (Jan 3, 2023)

Peter_SSs said:


> If this is still not sufficient, are you allowed to use macros?


Hi Peter, you are right, the formula has its limitations. And yes, I am allowed to use macros.


----------



## Peter_SSs (Jan 3, 2023)

We can investigate a vba solution, but I'm wondering about certain expected results. For example, 

What result would you want for row 2 below?
If we define a word as characters following a space or the string start and before the next space or the string end then 750 would be common. To us, that normally would not count as a word though.

What result would you want for row 3 below.
Defining a word as above then "ML" is common (& not digits), but not really a word.

What about row 4?
A bit similar to both the above examples except that "CAN" is a normal word - but nothing to do with the "Brand"

Anyway, eliminating certain punctuation (list is controlled by the 'Const' line in the code) and defining a word as described above, then you could consider this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)


```
Function Status(br1 As String, br2 As String) As Boolean
  Const PunctExcl As String = "( ) ' ," '<- List any characters to ignore with a space between each
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\" & Replace(PunctExcl, " ", "|\")
    br2 = .Replace(br2, "")
    .Pattern = "( |^)(" & Replace(.Replace(br1, ""), " ", "|") & ")( |$)"
    Status = .Test(br2)
  End With
End Function
```

PTR2022.xlsmABC1BRAND 1BRAND 2Status2CIDER CAN 750 MLSWINE AMAREN BOT 750 ML FRANCETRUE3CIDER CAN 275 MLWINE AMAREN BOT 750 ML FRANCETRUE4BRANDA CAN 275 MLBRANDB CAN 0.5LTRUE5AMAREN (ROOM ALEX CANAS)WINE AMAREN BOT 750 ML FRANCETRUE6OTHER (ROOM ALEX CANAS)ROOM BOT 750 ML FRANCETRUE7CONDESAABCONDESARA FLATFALSE8CONDESA SPARKLINGNON-SPARKLING CAVAFALSESheet1Cell FormulasRangeFormulaC2:C8C2=Status(A2,B2)


----------



## PTR2022 (Jan 4, 2023)

Hi peter, is it possible to send you a private message?


Peter_SSs said:


> We can investigate a vba solution, but I'm wondering about certain expected results. For example,
> 
> What result would you want for row 2 below?
> If we define a word as characters following a space or the string start and before the next space or the string end then 750 would be common. To us, that normally would not count as a word though.
> ...


Hi peter, is it possible to send you a private message?


----------



## Peter_SSs (Jan 5, 2023)

PTR2022 said:


> Hi peter, is it possible to send you a private message?


Yes it is. See this icon at the top right of the forum beside your user name.






However, please note that such messages should not be about forum threads or help with Excel - see # 4 of the Forum Rules
Any correspondence, follow-up questions etc about threads should be continued in the public thread.


----------

