In a list of 3 digit numbers from 000 to 999 I need to be able to identify the numbers that have at least 2 digits the same, could you please help me with a formula.
I tried =IF(LEFT(A98,1) = "LEFT(A98,2)"OR LEFT(A98,2) = "LEFT(A98,3)" OR LEFT(A98,1) ="LEFT(A98,3)" , "0","1")
. But I am very inexperienced at excel and it doesn't accept this formula.
3 Answers
That's actually not that far off.
Use MID()
instead of LEFT()
, because LEFT()
takes all the letters not just one for comparison.
Remove the unnecessary quotes, and it should work:
IF(OR(MID(A98,1,1)=MID(A98,2,1),MID(A98,1,1)=MID(A98,3,1),MID(A98,2,1)=MID(A98,3,1)),"1","0")
Make sure to understand the pieces of what I wrote, so you can correct it if I mistyped or forgot a bracket  I have no computer here to verify, and I am typing on a cell phone.

1
Aganju's answer is probably what you're looking for. A lot of times, though, the solution to an Excel problem comes from looking at it in different ways. Here's a little different approach with a couple of ideas you can keep in your back pocket.
LEFT, RIGHT, MID
Since everything is three digits, I find it easier to thinks of the digits as:
LEFT(A98,1)
MID(A98,2,1)
RIGHT(A98,1)
Just personal preference, and this will document how to use those functions. That's the leftmost 1 character, the middle starting at position 2 for a length of 1, and the rightmost 1 character.
Reverse Thinking
You approached this from the direction of finding any combination of matching digits. That requires some combination of IF, OR, and/or AND. If you approach it from the other direction, there's just one condition you want to exclude  all of the digits being different. Testing for that single case can be done more simply.
Boolean Arithmetic
I'll combine this with another trick. You're looking for a 1
or 0
result, so you're already thinking in terms of those representing true and false. You can use some math properties of 0
and 1
:
 zero times anything is zero
 multiplying a bunch of zeros yields zero
 multiplying a bunch of ones yields one
 the only way the product of a bunch of zeros or ones can yield one is if they're all ones
Putting it together
Excel treats true
and false
as1
and 0
. So without needing IF, AND, or OR, you can use:
= (LEFT(A98,1)<>MID(A98,2,1)) * (LEFT(A98,1)<>RIGHT(A98,1)) * (MID(A98,2,1)<>RIGHT(A98,1))
This yields 1
if all the digits are different and 0
if any combination is the same. That differentiates things. But if your mindset is that it should be the reverse, as you described in the question, you can reverse it by using 1
minus that formula:
= 1  (LEFT(A98,1)<>MID(A98,2,1)) * (LEFT(A98,1)<>RIGHT(A98,1)) * (MID(A98,2,1)<>RIGHT(A98,1))
And notice that the 0
or 1
values are produced directly by the logical tests; it wasn't necessary to assign these values to the result based on evaluating conditions.

2Smart, yet very hard to understand. If anybody else inspects the sheet and sees this they will not understand what the formula is testing. Jun 14, 2016 at 8:58
Here's another slightly different way, using @fixer1234's LEFT
MID
& RIGHT
and multiplication by zero idea:
=IF((LEFT(A99,1)MID(A99,2,1)) * (LEFT(A99,1)RIGHT(A99,1)) * (MID(A99,2,1)RIGHT(A99,1))=0,1,0)
if any of the pairs are equal, the result of the multiplication is zero, and the result of the IF
is 1.