Duplicate Digits

  • Thread starter Thread starter smandula
  • Start date Start date
S

smandula

Could there be a solution, without conditional formatting,
of being able to

Count duplicate or triplicate etc. digits within a single cell

For example
Data Count
A1 B1
5555 4
1112 3
1122 2
1234 0
1101 3


I have tried this formula with some success

=IF(MOD(A1,1111)=0,"4",IF(MOD(A1,111)=0,"3",IF(MOD(A1,11)=0,"2",))
for one Cell A1

Thanks
 
smandula said:
Could there be a solution, without conditional formatting,
of being able to

Count duplicate or triplicate etc. digits within a single cell

For example
Data Count
A1 B1
5555 4
1112 3
1122 2
1234 0
1101 3


I have tried this formula with some success

=IF(MOD(A1,1111)=0,"4",IF(MOD(A1,111)=0,"3",IF(MOD(A1,11)=0,"2",))
for one Cell A1
Click to expand...

If VBA is acceptable, then this works (for your sample data anyway). Put this
in a module:
Function MostRepeats(what)
Dim chk1 As Long, chk2 As Long, chk3 As Long
Dim digits(9) As Long
chk1 = what
While chk1 > 0
chk2 = chk1 Mod 10
chk1 = chk1 \ 10
digits(chk2) = digits(chk2) + 1
If digits(chk2) > chk3 Then chk3 = digits(chk2)
Wend
If chk3 > 1 Then MostRepeats = chk3
End Function

....and call it like this:
A B
1 5555 =MostRepeats(A1)
2 1112 =MostRepeats(A2)
3 1122 =MostRepeats(A3)
4 1234 =MostRepeats(A4)
5 1101 =MostRepeats(A5)
 
I said:
If VBA is acceptable, then this works (for your sample data anyway). Put
this in a module:
Click to expand...
[snip]

Should've mentioned that what I posted assumes positive whole numbers...
 
Back
Top