• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Modelo.xlsx
    9.6 KB · Views: 11
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

  • Copy of Modelo.xlsx
    10.5 KB · Views: 5
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))
 
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)
Shame about the 2016 :rolleyes:
 
typo in my previous answer
=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))
 
Please try
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

  • Modelo.xlsx
    11 KB · Views: 6
Bosco, Peter, Guido, Wizard, Good morning.

Thank you very much, excellent solutions work perfectly

Many thanks to the great masters

Decio
 
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:
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
 
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
 
Please try
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!
 
@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
 
Back
Top