Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Digits
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Digits
smandula wrote:
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 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) -- The key to being a genius is timing. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Digits
I wrote:
smandula wrote: 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 If VBA is acceptable, then this works (for your sample data anyway). Put this in a module: [snip] Should've mentioned that what I posted assumes positive whole numbers... -- Stupid clockwork reject. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Digits
Thanks!
That is a beautiful piece of code. It works very well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
number 12 digits to 15 digits | Excel Discussion (Misc queries) | |||
VBA to delete Duplicate Records (1 column), before which, non-duplicate data merged into remaining row | Excel Programming | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions |