separate cracks without typing.

[piter]

separate cracks without typing
example
18 20 36 = yes for 6 digits without repetition
18 20 38 = not because there is a repetition of the digit 8
there can not be in the separation of the cracks, cracks with
repetition of digits including zeros
http://www.mediafire.com/file/pib5xby336jtnq7/pasta_das_trincas.xlsx

 

[Leith Ross]

Hello piter,

I added this macro to the worksheets Change Event. It will evaluate the values as type into the cells in columns "A:C". If there are no repeating numbers and the numbers contain only 2 digits then the numbers are output in the same row to column "E".

pasta das trincas ver 1.xlsm

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Cell    As Range
    Dim Digits  As Variant
    Dim j       As Integer
    Dim k       As Integer
    Dim n       As Integer
    Dim Repeats As Boolean
    Dim Rng     As Range
    Dim Text    As String
    
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
        If Len(Target.Text) <> 2 Then Exit Sub
        If Not IsNumeric(Target) Then Exit Sub
        
        Set Rng = Range(Cells(Target.Row, "A"), Cells(Target.Row, "C"))
        
        ReDim Digits(9)
        
            For Each Cell In Rng
                n = Cell.Value
                j = n Mod 10
                k = n \ 10
                Digits(j) = Digits(j) + 1
                Digits(k) = Digits(k) + 1
            Next Cell
            
            For n = 0 To 9
                If Digits(n) > 1 Then Repeats = True
            Next n
            
        If Not Repeats Then
            For n = 1 To 3
                If Text = "" Then
                    Text = Format(Cells(Target.Row, n), "00")
                Else
                    Text = Text & "," & Format(Cells(Target.Row, n), "00")
                End If
            Next n
            Cells(Target.Row, "E") = Text
        End If
        
End Sub

 

[piter]

Hello and the others? Type = 04 28 37?
They're all cracks !!! Within the condition of not having repeated digits

 

[piter]

but the excluded cracks can have 3,4 double digits not only 2 you filter or separate in a list only the cracks with 6 different digits or separtete duplicates of digits

 

[piter]

hello ROSS We have a problem, the macro is not doing it!
With six different digits

 

[Leith Ross]

Hello Piter,

It appears I misunderstood your request. Can you provide me a couple of before and after examples of how this should work?

 

[piter]

hello,ROSS from the list given, you have to separate all the formations
with 6 different digits
10 26 38
08 14 36

 

[Peter_SSs]

.. from the list given, you have to separate all the formations
with 6 different digits
10 26 38
08 14 36

Try this in a copy of your workbook.

Sub piter()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim s As String, d As String
  Dim bFail As Boolean
  
  a = Range("A5", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  
  For i = 1 To UBound(a)
    s = Format(a(i, 1), "00") & "," & Format(a(i, 2), "00") & "," & Format(a(i, 3), "00")
    bFail = False
    For j = 2 To Len(s)
      d = Mid(s, j, 1)
      If d Like "#" Then
        If InStr(1, s, d) < j Then
          bFail = True
          Exit For
        End If
      End If
    Next j
    If Not bFail Then
      k = k + 1
      b(k, 1) = s
    End If
  Next i
  If k > 0 Then
    Range("E5").Resize(k).Value = b
  End If
End Sub

 

[piter]

yes, ok PETER, PERFECT,THANK YOU!

 

[piter]

Hello, Peter, using the same code
I have, a quadruple file of 850 thousand or more
Example
02 15 26 35
That is, using the same code to remove duplicates from the quadruples