CODEWITHSUNDEEP
excelexcel-formula
Rasec Malkic
Rasec Malkic

Reputation: 671

Formula to get common numbers between 2 values

I'm trying to get the common numbers from the columns below (A and B) in order to

The expected result logic is described in column COMMENT

The output is fine in all scenarios, except when value in A = value in B. In this case the formula should show the same value in A and is showing empty. Thanks for any help. (working in Excel 2016)

My current Array formula is {=IFERROR(LEFT(A2,MATCH(FALSE,(MID($A2,ROW($L$1:$L$10),1)=MID($B2,ROW($L$1:$L$10),1)),0)-1),"")}

A B CURRENT RESULT EXPECTED RESULT SCENARIO
0000 9999 Any number is equal then show empty
149 149 149 All numbers are equal then show all numbers
2000 2499 2 2 First number is equal then show first number
6671000 6671009 667100 667100 First 6 numbers are equal then show first 6 numbers

Upvotes: 4

Views: 696

Answers (5)

JvdV
JvdV

Reputation: 75900

Another alternative:

enter image description here

Formula in C1:

=REDUCE("",LEFT(A1,SEQUENCE(LEN(A1))),LAMBDA(a,b,IF(COUNTIF(B1,b&"*"),b,a)))

Another one:

=LET(x,LEN(A1),y,LEFT(A1,SEQUENCE(x,,x,-1)),@FILTER(y,COUNTIF(B1,y&"*"),""))

Even shorter:

=LET(a,LEFT(A1,SEQUENCE(LEN(A1))),XLOOKUP(1,SEARCH(a&"*",B1),a,"",,-1))

Or even shorter:

=LET(x,SEQUENCE(LEN(A1)),LEFT(A1,MAX(x*(LEFT(A1,x)=LEFT(B1,x)))))

Upvotes: 5

user11222393
user11222393

Reputation: 5471

As OP proposed, putting my comment to an answer:

{=LEFT(A2,MATCH(FALSE,(MID($A2,ROW($L$1:$L$10),1)=MID($B2,ROW($L$1:$L$10),1)),-1)-1)}

It is suited for Excel 2016 version, but there is character limitation with ROW($L$1:$L$10). Comparing longer strings should change it to ROW($L$1:$L$100) or something (max string length + 1), or limitless to COLUMN($1:$1) as @David Leal suggested.

Result:

enter image description here

Upvotes: 6

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Following formula will not require Array entry and is giving results as expected in the sample data.

=IFERROR(LOOKUP(2,1/(LEFT(A2,ROW($A$1:$A$10))=LEFT(B2,ROW($A$1:$A$10))),LEFT(A2,ROW($A$1:$A$10))),"")

Edit:

If one wants to make it flexible for character length then it will be clunky and semi-volatile due to INDEX usage:

=IFERROR(LOOKUP(2,1/(LEFT(A2,ROW($A$1:INDEX(A:A,MIN(LEN(A2),LEN(B2)))))=LEFT(B2,ROW($A$1:INDEX(A:A,MIN(LEN(A2),LEN(B2)))))),LEFT(A2,ROW($A$1:INDEX(A:A,MIN(LEN(A2),LEN(B2)))))),"")

Upvotes: 4

David Leal
David Leal

Reputation: 6759

Just a variation of the solution proposed by @user11222393 in the comment section of the question to include a general case (the solution assumes a partial match up to 9 digits):

=LEFT(A2,MATCH(FALSE,MID(A2,SEQUENCE(LEN(A2)+1),1)
  =MID(B2,SEQUENCE(LEN(B2)+1),1),-1)-1)

Here is the output: output

Upvotes: 4

SE1986
SE1986

Reputation: 2760

Lets break down your criteria one by one:

Any number is equal then show empty

This needs more clarity - are you saying if there are any character repetitions in the strings in A or B, a blank string would be returned?

The remainder, I think I understood from your question:

All numbers are equal then show all numbers

if the value in A matches B, return the matching value:

=IF(A1=B1,A1)

First number is equal then show first number

If the first digit of the value in A and B match, return that digit

=IF(LEFT(A1,1)=LEFT(B1,1),LEFT(A1,1))

First 6 numbers are equal then show first 6 numbers

If the first six digits of the value in A and B match, return those digits

=IF(LEFT(A1,6)=LEFT(B1,6),LEFT(A1,6))

We can then bring these sub-criteria into one formula

=IF(A1=B1,A1,IF(LEFT(A1,1)=LEFT(B1,1),LEFT(A1,1),IF(LEFT(A1,6)=LEFT(B1,6),LEFT(A1,6),"no criteria matched")))

Upvotes: 0

Related Questions