Generating 3 digit code

[hakimjah]

Hi,
My objective is to produce a list of all the numbers of three non-similar digits which do not contain digits 0, 1 & 7.
May you please advice.
Thanks.

 

[Ruddles]

This in A1, then copy down to A1000:-

=IF(AND(LEFT(TEXT(ROW(),"000"),1)<>MID(TEXT(ROW(),"000"),2,1),LEFT(TEXT(ROW(),"000"),1)<>RIGHT(TEXT(ROW(),"000"),1),MID(TEXT(ROW(),"000"),2,1)<>RIGHT(TEXT(ROW(),"000"),1),LEN(TEXT(ROW(),"000"))=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(ROW(),"000"),"0",""),"1",""),"7",""))),TEXT(ROW(),"000"),"")

Any good?

 

[Eric W]

Welcome to the forum.

Ruddles' formula works fine, but it leaves gaps in the column for the excluded numbers. I made a tiny tweak to that formula (removed the TEXT from around the ROW() at the end), then added another formula in B1 that groups the remaining numbers like so:

Excel 2012

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]234[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]235[/TD]
[TD="align: right"][/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]235[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]236[/TD]
[TD="align: right"][/TD]
[TD="align: right"]236[/TD]
[TD="align: right"]236[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]238[/TD]
[TD="align: right"][/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]238[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]239[/TD]
[TD="align: right"][/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]239[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]243[/TD]
[TD="align: right"][/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]243[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]245[/TD]
[TD="align: right"][/TD]
[TD="align: right"]243[/TD]
[TD="align: right"]245[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]246[/TD]
[TD="align: right"][/TD]
[TD="align: right"]244[/TD]
[TD="align: right"]246[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]248[/TD]
[TD="align: right"][/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]248[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]249[/TD]
[TD="align: right"][/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]249[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]253[/TD]
[TD="align: right"][/TD]
[TD="align: right"]248[/TD]
[TD="align: right"]253[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]254[/TD]
[TD="align: right"][/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]254[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]256[/TD]
[TD="align: right"][/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]256[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]258[/TD]
[TD="align: right"][/TD]
[TD="align: right"]253[/TD]
[TD="align: right"]258[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]259[/TD]
[TD="align: right"][/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]259[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=IF(AND(LEFT(TEXT(ROW(),"000"),1)<>MID(TEXT(ROW(),"000"),2,1),LEFT(TEXT(ROW(),"000"),1)<>RIGHT(TEXT(ROW(),"000"),1),MID(TEXT(ROW(),"000"),2,1)<>RIGHT(TEXT(ROW(),"000"),1),LEN(TEXT(ROW(),"000"))=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(ROW(),"000"),"0",""),"1",""),"7",""))),ROW(),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=SMALL($A1:$A$1000,ROW())[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=SMALL(IF(MMULT(ISNUMBER(FIND({0,1,7},ROW(INDIRECT("234:986"))&""))+0,{1;1;1}),"",ROW(INDIRECT("234:986"))),ROWS($D$1:$D1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=SMALL(IF(MMULT(IF(LEN(SUBSTITUTE($D$1:$D$325&"",MID($D$1:$D$325,{1,2,3},1),""))<2,1,0),{1;1;1}),"",$D$1:$D$325),ROWS($E$1:$E1))}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





Then I went at it from another angle. The D1 formula creates a list of numbers excluding 0,1,7 with no gaps. The E1 formula looks at that list and removes numbers with duplicate digits. Combining those formulas is possible, but NOT advisable! Since both those formulas have a lot of array processing, they are VERY slow, and combining them would make it worse. Ruddles' formula is probably faster, even with the helper column in B.

Someone else may come up with a more elegant formula solution, but this kind of thing is usually best left to VBA. Here's a macro that calculates the list:

Sub NumList()
Dim r As Long, i As Long, stri As String, CalcCode As Long

    Application.ScreenUpdating = False
    CalcCode = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    r = 0
    For i = 234 To 986
        stri = i
        If Not stri Like "*[017]*" Then
            If Mid(stri, 1, 1) = Mid(stri, 2, 1) Or _
               Mid(stri, 1, 1) = Mid(stri, 3, 1) Or _
               Mid(stri, 2, 1) = Mid(stri, 3, 1) Then
            Else
                r = r + 1
                Cells(r, "E") = i
            End If
        End If
    Next i
    
    Application.ScreenUpdating = True
    Application.Calculation = CalcCode
    
End Sub

Hope you find something useful here!