• 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

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)
Shame about the 2016 :rolleyes:
 

GraH - Guido

Well-Known Member
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))
 

Excel Wizard

Active Member
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

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
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!
 

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
 
Top