# Find differences between codes

#### deciog

##### Active Member
Good morning and Happy New Year everyone!

It is possible to create a non-VBA formula, to find and identify a difference between codes ?, it can have more than one different number

The formula created in cell H3 should return the result: "Difference found: ******7**

Version Excel 2016 and also 365

Thank you very much in advance

#### Attachments

• 9.6 KB Views: 11

#### GraH - Guido

##### Well-Known Member
365 option
=TEXTJOIN("",TRUE,SUBSTITUTE(SUBSTITUTE(MID(D3,SEQUENCE(LEN(D3)),1)<>MID(F3,SEQUENCE(LEN(D3)),1),TRUE,MID(F3,SEQUENCE(LEN(D3)),1)),FALSE,"*"))

#### Attachments

• 10.5 KB Views: 5

#### bosco_yip

##### Excel Ninja
Or this long formula without Textjoin & Sequence function :

=IF(MID(D3,1,1)=MID(F3,1,1),"*",MID(F3,1,1))&IF(MID(D3,2,1)=MID(F3,2,1),"*",MID(F3,2,1))&IF(MID(D3,3,1)=MID(F3,3,1),"*",MID(F3,3,1))&IF(MID(D3,4,1)=MID(F3,4,1),"*",MID(F3,4,1))&IF(MID(D3,5,1)=MID(F3,5,1),"*",MID(F3,5,1))&IF(MID(D3,6,1)=MID(F3,6,1),"*",MID(F3,6,1))&IF(MID(D3,7,1)=MID(F3,7,1),"*",MID(F3,7,1))&IF(MID(D3,8,1)=MID(F3,8,1),"*",MID(F3,8,1))&IF(MID(D3,9,1)=MID(F3,9,1),"*",MID(F3,9,1))&IF(MID(D3,10,1)=MID(F3,10,1),"*",MID(F3,10,1))

#### Peter Bartholomew

##### Well-Known Member
365 and I am spoilt for choice!
Code:
``````= LET(
different?, EXPLODEλ(Codigo1) <> EXPLODEλ(Codigo2),
differences, IF(different?, EXPLODEλ(Codigo2),"*"),
CONCAT(differences)
)

where EXPLODEλ is given by

= LAMBDA(code,
LET(
k, SEQUENCE(LEN(code)),
chr, MID(code, k, 1),
chr
)
)``````
Alternatively, working character by character
Code:
``````= LAMBDA(string1,string2,
REDUCE("",SEQUENCE(LEN(string1)),
LAMBDA(result,k,
LET(
chr₁, MID(string1,k,1),
chr₂, MID(string2,k,1),
result&IF(chr₁=chr₂, "*", chr₂)
)
)
)
)(Codigo1,Codigo2)``````
or
Code:
``````= LAMBDA(string1,string2,
CONCAT(
MAP(SEQUENCE(LEN(string1)),
LAMBDA(k,
LET(
chr₁, MID(string1,k,1),
chr₂, MID(string2,k,1),
IF(chr₁=chr₂, "*", chr₂)
)
)
)
)
)(Codigo1,Codigo2)``````

The file

#### Attachments

• 11.3 KB Views: 5

#### GraH - Guido

##### Well-Known Member
=TEXTJOIN("",TRUE,SUBSTITUTE(SUBSTITUTE(MID(D3,SEQUENCE(LEN(D3)),1)<>MID(F3,SEQUENCE(LEN(F3)),1),TRUE,MID(F3,SEQUENCE(LEN(F3)),1)),FALSE,"*"))
+ another let
=LET(d,MID(D3,SEQUENCE(LEN(D3)),1),dd,MID(F3,SEQUENCE(LEN(F3)),1),r,SWITCH(d<>dd,TRUE,dd,FALSE,"*"),TEXTJOIN("",TRUE,r))

#### Excel Wizard

##### Active Member
365
=LET(s,SEQUENCE(10),t,MID(F3,s,1),CONCAT(IF(MID(D3,s,1)=t,"*",t)))

other version

=SUBSTITUTE(TEXT(NPV(9,IF(MID(D3,ROW(A\$1:A\$10),1)=MID(F3,ROW(A\$1:A\$10),1),0,MID(F3,ROW(A\$1:A\$10),1))*10^10),SUBSTITUTE(SUBSTITUTE(TEXT(NPV(9,IF(MID(D3,ROW(A\$1:A\$10),1)=MID(F3,ROW(A\$1:A\$10),1),0,1)*10^10),REPT(0,10)),0,"-0"),1,0)),"-0","*")

#### Attachments

• 11 KB Views: 6

#### deciog

##### Active Member
Bosco, Peter, Guido, Wizard, Good morning.

Thank you very much, excellent solutions work perfectly

Many thanks to the great masters

Decio

#### deciog

##### Active Member
Wizard, reporting

This function is not showing zero value, all the others are great

=SUBSTITUTE(TEXT(NPV(9,IF(MID(D3,ROW(A\$1:A\$10),1)=MID(F3,ROW(A\$1:A\$10),1),0,MID(F3,ROW(A\$1:A\$10),1))*10^10),REPT(0,10)),0,"*")

Many thanks to the great masters

Decio

Last edited:

#### deciog

##### Active Member
Peter

The company now has 120 branches and is changing little by little, so it still has an old version, but they will change everything

decio

#### GraH - Guido

##### Well-Known Member
Allow me this plaything with Power Query
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColsAsText = Table.TransformColumnTypes(Source,{{"Codigo 1", type text}, {"Codigo 2", type text}}),
ComparedStrings = Table.AddColumn(ColsAsText, "Compare", each Text.Combine(List.Transform(List.Zip({Text.ToList([Codigo 2]),Text.ToList([Codigo 1])}), each if _{0} = _{1} then "*" else _{0}),""))
in
ComparedStrings``````

#### Peter Bartholomew

##### Well-Known Member
365
=LET(s,SEQUENCE(10),t,MID(F3,s,1),CONCAT(IF(MID(D3,s,1)=t,"*",t)))

other version

=SUBSTITUTE(TEXT(NPV(9,IF(MID(D3,ROW(A\$1:A\$10),1)=MID(F3,ROW(A\$1:A\$10),1),0,MID(F3,ROW(A\$1:A\$10),1))*10^10),SUBSTITUTE(SUBSTITUTE(TEXT(NPV(9,IF(MID(D3,ROW(A\$1:A\$10),1)=MID(F3,ROW(A\$1:A\$10),1),0,1)*10^10),REPT(0,10)),0,"-0"),1,0)),"-0","*")
I looked on this solution with a mixture of admiration and horror! By the time I had picked out parts of the solution for examination I finished up with
Code:
``````= LET(
k,      SEQUENCE(10),
chr₁,   MID(Codigo1,k,1),
chr₂,   MID(Codigo2,k,1),
match?, chr₁=chr₂,
x,      NPV(9,IF(match?,0,chr₂)*10^10),
z,      NPV(9,IF(match?,0,1)*10^10),
format, REPT(0,10),
text1,  SUBSTITUTE(TEXT(z,format),0,"-0"),
text2,  TEXT(x,SUBSTITUTE(text1,1,0)),
result, SUBSTITUTE(text2,"-0","*"),
result )``````
The NPV is really devious, but effective, and the SUBSTITUTES are 'interesting' to follow.
ps. I am so glad I have settled on 365; I would go with the first solution any day!

#### deciog

##### Active Member
Peter

Works perfectly great solution

Hugs

decio

#### Peter Bartholomew

##### Well-Known Member
@deciog
The last solution is simply the 'other' solution put forward by @Excel Wizard, just laid out using LET. Just comparing his two solutions makes it clear what immense strides have been made with modern Excel.

#### deciog

##### Active Member
@Peter

I really appreciate the examples you do, they are very useful for learning, the examples using the REDUCE, MAP and MAKEARRAY functions are very good, I'm learning a lot.

Thanks

Decio