Looking for some help to find "missing" letter groups

**Larbec** · 12-04-2015, 04:46 PM

I am sure i did something wrong with this Macro. I am trying to find all the possible combinations of EEEEEE OOOOOO and UUUUUU DDDDDD. The Macro for all the possible combinations is only part of what I need.

Once I can figure that out all the possible combinations I would like to have a Macro that can look through a list of say 2000 rows x 6 columns and find out which combinations are not in the list yet of possible combinations and either high light them or extract them to a different sheet. 1 Macro wilL do the EEEEEE OOOOOO and the other will do the UUUUUU DDDDDD. If not this way then perhaps pull out all the combinations that are in the 2001 rows and I can manually compare them to those in the list of "all" the possible combinations.

This is what I have so far with a macro to find all the different combinations of the EEEEEE OOOOOO but I know its wrong and I do not know where my mistakes are.

Sub testit()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim r As Integer
    For i = 1 To 2
        For j = 1 To 2
            For k = 1 To 2
                r = r + 1
                Cells(r, 1) = IIf(i = 1, "E", "O")
                Cells(r, 2) = IIf(j = 1, "E", "O")
                Cells(r, 3) = IIf(k = 1, "E", "O")
                Cells(r, 4) = IIf(i = 1, "E", "O")
                Cells(r, 5) = IIf(j = 1, "E", "O")
                Cells(r, 6) = IIf(k = 1, "E", "O")
            Next k
        Next j
    Next i
    
End Sub

which gave me

E	E	E	E	E	E
E	E	O	E	E	O
E	O	E	E	O	E
E	O	O	E	O	O
O	E	E	O	E	E
O	E	O	O	E	O
O	O	E	O	O	E
O	O	O	O	O	O

I know I am missing quite a few combinations for example: EOEOEO OR OEOEOE is not in the above list

Is this something that can be done or will it be a lot of effort? Thank you so much for all your expertise and help

This is how my sheets are laid out with the exception they will go to 2001 rows

**SamT** · 12-04-2015, 07:42 PM

There are 64 possible variations. 64 is a short enough loop that you might as well just add them to a collection each time you need them

Option Explicit

Dim References As Collection
Dim UsedVariations As Scripting.Dictionary

Sub InitializeReferences()
'P 1 - P6 for Position, i1 to i6 for loops. Using Variants to minimize typing
Dim P1, P2, P3, P4, P5, P6
Dim i1, i2, i3, i4, i5, i6

For i1 = 0 To 1
For i2 = 0 To 1
For i3 = 0 To 1
For i4 = 0 To 1
For i5 = 0 To 1
For i6 = 0 To 1
If i1 Then
 P1 = "E"
Else: P1 = "O"
End If
If i2 Then
 P2 = "E"
Else: P2 = "O"
End If
If i3 Then
 P3 = "E"
Else: P3 = "O"
End If
If i4 Then
 P4 = "E"
Else: P4 = "O"
End If
If i5 Then
 P5 = "E"
Else: P5 = "O"
End If
If i6 Then
 P6 = "E"
Else: P6 = "O"
End If
References.Add P1 & P2 & P3 & P4 & P5 & P6
Next
Next
Next
Next
Next
Next

For i1 = 0 To 1
For i2 = 0 To 1
For i3 = 0 To 1
For i4 = 0 To 1
For i5 = 0 To 1
For i6 = 0 To 1
If i1 Then
 P1 = "D"
Else: P1 = "U"
End If
If i2 Then
 P2 = "D"
Else: P2 = "U"
End If
If i3 Then
 P3 = "D"
Else: P3 = "U"
End If
If i4 Then
 P4 = "D"
Else: P4 = "U"
End If
If i5 Then
 P5 = "D"
Else: P5 = "U"
End If
If i6 Then
 P6 = "D"
Else: P6 = "U"
End If
References.Add P1 & P2 & P3 & P4 & P5 & P6
Next
Next
Next
Next
Next
Next

End Sub

Now Create a Dictionary and add all the used variations. Requires a reference to Microsoft Scripting Runtime.

Sub IntializeDictionary()
Dim Rw As Long
Dim WsF As Object
Set WsF = Application.WorksheetFunction

With Sheets("unknown").Columns(3) 'edit Sheet name to suit
  For Rw = 4 To 2001
    UsedVariations.Add Key:=WsF.Concatenate(.Cells(Rw).Resize(1, 42)), Item:="Used"
  Next Rw
End With
End Sub

Here's how to use them

Sub Demo()
Dim i As Long
InitialzeReferences
IntitializeDictionary

For i = 1 To References.Count
If Not UsedVariations.Exists(References(i)) Then
MsgBox "The Combination " & References(i) & " Was not used"
End If
Next i
End Sub

**snb** · 12-05-2015, 07:55 AM

Sub M_snb()
    sn = [index(mid("EOUD",int((row(1:4096)-1)/4^5)+1,1)&mid("EOUD",int(mod((row(1:4096)-1),4^5)/4^4)+1,1)&mid("EOUD",int(mod((row(1:4096)-1),4^4)/4^3)+1,1)&mid("EOUD",int(mod((row(1:4096)-1),4^3)/4^2)+1,1)&mid("EOUD",int(mod((row(1:4096)-1),4^2)/4)+1,1),)]

    Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

**Larbec** · 12-05-2015, 09:24 AM

Sam,

And this is when I get lost as to what to do next. I know I have to put the Macro in a module but when I do I get an error. I have uploaded a sample spreadsheet. Can you do the placement for me so I can see how it is done for the next time? When i do it I get an error.

P error.jpg

thank you

**SamT** · 12-05-2015, 11:19 AM

I should not have used the Keyword "References" but that was not the main or only problem. Did you know that the Tab Name of Sheet EO was really EO+space+space?

This works and I kept it simple with no advanced code "tricks"

Option Explicit
 
Dim Combinations As New Collection
Dim UsedVariations As New Scripting.Dictionary


Sub InitializeCombinations()
     'P 1 - P6 for Position, i1 to i6 for loops. Using Variants to minimize typing
    Dim P1, P2, P3, P4, P5, P6
    Dim i1, i2, i3, i4, i5, i6
     
    For i1 = 0 To 1
        For i2 = 0 To 1
            For i3 = 0 To 1
                For i4 = 0 To 1
                    For i5 = 0 To 1
                        For i6 = 0 To 1
                            If i1 Then
                                P1 = "E"
                            Else: P1 = "O"
                            End If
                            If i2 Then
                                P2 = "E"
                            Else: P2 = "O"
                            End If
                            If i3 Then
                                P3 = "E"
                            Else: P3 = "O"
                            End If
                            If i4 Then
                                P4 = "E"
                            Else: P4 = "O"
                            End If
                            If i5 Then
                                P5 = "E"
                            Else: P5 = "O"
                            End If
                            If i6 Then
                                P6 = "E"
                            Else: P6 = "O"
                            End If
                            Combinations.Add P1 & P2 & P3 & P4 & P5 & P6
                        Next
                    Next
                Next
            Next
        Next
    Next
     
    For i1 = 0 To 1
        For i2 = 0 To 1
            For i3 = 0 To 1
                For i4 = 0 To 1
                    For i5 = 0 To 1
                        For i6 = 0 To 1
                            If i1 Then
                                P1 = "D"
                            Else: P1 = "U"
                            End If
                            If i2 Then
                                P2 = "D"
                            Else: P2 = "U"
                            End If
                            If i3 Then
                                P3 = "D"
                            Else: P3 = "U"
                            End If
                            If i4 Then
                                P4 = "D"
                            Else: P4 = "U"
                            End If
                            If i5 Then
                                P5 = "D"
                            Else: P5 = "U"
                            End If
                            If i6 Then
                                P6 = "D"
                            Else: P6 = "U"
                            End If
                            Combinations.Add P1 & P2 & P3 & P4 & P5 & P6
                        Next
                    Next
                Next
            Next
        Next
    Next
     
End Sub

Sub InitializeDictionary(ShtName As String)
    Dim Rw As Long
    Dim i As Long
    Dim KeyName
     '.Columns(3) 'edit Sheet name to suit
      For Rw = 4 To 2004
        For i = 3 To 37
          KeyName = KeyName & .Cells(Rw, i)
        Next i
        If Len(KeyName) <> 6 Then MsgBox "Row " & Rw & " Combo is " & KeyName
          If Not UsedVariations.Exists(KeyName) Then UsedVariations.Add KeyName, "Used"
          KeyName = ""
      Next Rw
    End With
End Sub

Sub Demo()
    Dim i As Long
    Set UsedVariations = Nothing
    InitializeCombinations
    InitializeDictionary "EO  " 'Note Spaces in sheet name
    Dim Answer
     
    For i = 1 To Combinations.Count
        If Not UsedVariations.Exists(Combinations(i)) Then
            MsgBox "The Combination " & Combinations(i) & " Was not used"
        End If
        
        Answer = MsgBox("Press Cancel to stop showing this message", vbOKCancel)
        If Answer = vbCancel Then GoTo CleanExit
    Next i
    
CleanExit:
Dim X
X = Combinations.Count / 2 - 1
MsgBox "There were " & X - UBound(UsedVariations.Keys) & " unused Combinations"
    Set Combinations = Nothing
    Set UsedVariations = Nothing
End Sub

**Larbec** · 12-05-2015, 12:20 PM

Sam,

I did not know about the EO+space+space. That bit me on the lst Macro so I need to pay closer attention. When I place this in the Module and run it gives me a compile error

Compile Error.jpg

When i make my buttons, do I make 1 for the Demo and 1 for the initialize? What does each one do? Once again, THANK YOU for your valuable time!!!!

EDIT.....I just noticed it said to edit the sheet names.

sheet names.jpg

Do I change something in here?

Sub InitializeDictionary(ShtName As String) 
    Dim Rw As Long 
    Dim i As Long 
    Dim KeyName 
     '.Columns(3) 'edit Sheet name to suit
    For Rw = 4 To 2004 
        For i = 3 To 37 
            KeyName = KeyName & .Cells(Rw, i) 
        Next i 
        If Len(KeyName) <> 6 Then MsgBox "Row " & Rw & " Combo is " & KeyName 
        If Not UsedVariations.Exists(KeyName) Then UsedVariations.Add KeyName, "Used" 
        KeyName = "" 
    Next Rw 
End With 
End Sub 

I really am trying to learn and I appreciate your patience

**Paul_Hossler** · 12-06-2015, 08:02 AM

I think you need to add manually add a Reference

In the VBE, [Tool], [References...] and check Microsoft Scripting Runtime

Capture.JPG

**Paul_Hossler** · 12-06-2015, 10:07 AM

Another way that uses arrays and MATCH() to see if it's there

Option Explicit

Dim EO(1 To 2 ^ 6) As String, DU(1 To 2 ^ 6) As String

Sub InitializeReferences_2()
    Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long
    Dim n As Long
    
    n = 1
    For i1 = 0 To 1
        For i2 = 0 To 1
            For i3 = 0 To 1
                For i4 = 0 To 1
                    For i5 = 0 To 1
                        For i6 = 0 To 1
                            EO(n) = IIf(i1 = 0, "E", "O") & IIf(i2 = 0, "E", "O") & IIf(i2 = 0, "E", "O") & _
                                IIf(i4 = 0, "E", "O") & IIf(i5 = 0, "E", "O") & IIf(i6 = 0, "E", "O")
                            DU(n) = IIf(i1 = 0, "D", "U") & IIf(i2 = 0, "D", "U") & IIf(i2 = 0, "D", "U") & _
                                IIf(i4 = 0, "D", "U") & IIf(i5 = 0, "D", "U") & IIf(i6 = 0, "D", "U")
                            n = n + 1
                        Next
                    Next
                Next
            Next
        Next
    Next
End Sub
 
Sub drv()
    Dim v As Variant
    InitializeReferences_2
    On Error Resume Next
    With Application.WorksheetFunction
        v = -1
        v = .Match("EOOEOO", EO, 0)
        MsgBox (v <> -1)
        v = -1
        v = .Match("DUUDUU", DU, 0)
        MsgBox (v <> -1)
        v = -1
        v = .Match("ABCDEF", EO, 0)
        MsgBox (v <> -1)
    End With
    On Error GoTo 0
    
End Sub

Probably more elegant ways, but this seems most straight forward (IMHO)

**SamT** · 12-06-2015, 10:17 AM

Thanks, Paul. My post in re References must be lost in the Quantum Sea of Electrons.

**Larbec** · 12-06-2015, 11:02 AM

Ive done as instructed and get a different compile error. Also, what is supposed to happen when I run the file that says "drv" A box appears that says TRUE, If I click on it another box says TRUE, I click on that it says FALSE and nothing else happens.

I am trying to run these on the same file I uploaded in post #4

pic 1 compile error.jpg

**Paul_Hossler** · 12-06-2015, 11:16 AM

Also, what is supposed to happen when I run the file that says "drv" A box appears that says TRUE, If I click on it another box says TRUE, I click on that it says FALSE and nothing else happens.

That's all drv is supposed to do

I was just demonstrating one easy way to see if a value is in an array after the 64 element array with all possible permutations for 2 letters was generated.
Fitting it into the macro for 2001+ rows is left as an exercise to the reader
You could easily turn the logic into a simple function if you wanted to

1. It loads all 64 permutations of the letter pairs into two arrays
2. It looks to see if a 6-letter permutation in is the array
3. The first two EOOEOO and DUUDUU were in the EO and DU arrays respectively
4. The third was a failure test since ABCDEF was not in the array

 
Sub drv() 
    Dim v As Variant 
    InitializeReferences_2 
    On Error Resume Next 
    With Application.WorksheetFunction 
        v = -1 
        v = .Match("EOOEOO", EO, 0) 
        MsgBox (v <> -1) 
        v = -1 
        v = .Match("DUUDUU", DU, 0) 
        MsgBox (v <> -1) 
        v = -1 
        v = .Match("ABCDEF", EO, 0) 
        MsgBox (v <> -1) 
    End With 
    On Error GoTo 0 
     
End Sub

**Larbec** · 12-06-2015, 11:34 AM

I understand now.
What about the other part I was asking for?

"Once I can figure that out all the possible combinations I would like to have a Macro that can look through a list of say 2000 rows x 6 columns and find out which combinations are not in the list yet of possible combinations, and either high light them or extract them to a different sheet. 1 Macro wilL do the EEEEEE OOOOOO and the other will do the UUUUUU DDDDDD. If not this way then perhaps pull out all the combinations that are in the 2001 rows and I can manually compare them to those in the list of "all" the possible combinations."

Does this make sense? I basically want to be able to view (look at) all the different permutations that each letter group can make (64 permutations) (see it on a spreadsheet) and then go through the 2000 rows and compare it to the list and see what is absent or not in that list of 64.

**snb** · 12-06-2015, 01:44 PM

I'd use:

Sub M_snb()
    Cells(1).Resize(64) = [index(substitute(substitute(text(dec2bin(row(1:64)-1),"'000000"),"1","E"),"0","O"),)]
    Cells(65, 1).Resize(64) = [index(substitute(substitute(text(dec2bin(row(1:64)-1),"'000000"),"1","U"),"0","D"),)]
End Sub

**Paul_Hossler** · 12-06-2015, 02:24 PM

[QUOTE][Does this make sense? I basically want to be able to view (look at) all the different permutations that each letter group can make (64 permutations) (see it on a spreadsheet) and then go through the 2000 rows and compare it to the list and see what is absent or not in that list of 64/QUOTE]

I don't see what you want to do on the spreadsheet, but you could use COUNTIF (like in sub CountEO below)

I just made up some data in the attachment sheet 'Data'

Option Explicit

Sub FillEO()
    Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long
    Dim n As Long
     
    Application.ScreenUpdating = False
    With Worksheets("EO")
        n = 1
        For i1 = 0 To 1
            For i2 = 0 To 1
                For i3 = 0 To 1
                    For i4 = 0 To 1
                        For i5 = 0 To 1
                            For i6 = 0 To 1
                                .Cells(n, 1).Value = IIf(i1 = 0, "E", "O") & IIf(i2 = 0, "E", "O") & IIf(i2 = 0, "E", "O") & _
                                IIf(i4 = 0, "E", "O") & IIf(i5 = 0, "E", "O") & IIf(i6 = 0, "E", "O")
                                n = n + 1
                            Next
                        Next
                    Next
                Next
            Next
        Next
    End With
    Application.ScreenUpdating = True
End Sub
 
 
 Sub CountEO()
    Dim i As Long
    Dim rData As Range
 
    Set rData = Worksheets("Data").Cells(1, 1).CurrentRegion
 
    Application.ScreenUpdating = False
    With Worksheets("EO")
        For i = 1 To 64
            .Cells(i, 2).Value = Application.WorksheetFunction.CountIf(rData, .Cells(i, 1).Value)
        Next I
    End With
    Application.ScreenUpdating = True
 
 End Sub

**Larbec** · 12-06-2015, 03:02 PM

Paul,

Let me try to explain this differently and also show you in the attached file i have for sorting the EOs and UDs horizontally. In the attached file I can place my history in sheet1. Then I place my EOUD's on the sheet called parameters in cell B9 that I want to find in sheet1. This is what I want to look for in my history. When you click on the button Veni,vidi,vici it finds all the same letters as in B9 PLUS the row above it and places the answers in sheet1_Out

I want to do similar sorting as the attached file except i want to find what is absent.

I need 2 things ....

1. to see "all" the combinations that are possible with the 6 letter groups (EEEEEE OOOOOO) and UUUUUU DDDDDD) what you attached i think does that on the sheet called EO so I would like to do that for the UD also. I am not sure what the numbers in column B are

2. be able to search through any given data (EOs and UDs) I place in sheet1 and the answers go in sheet1_Out ........ I want to find the data that is not within the 64 combinations and place that answer(s) in sheet1_Out ...... "or" get a list of all the combinations out of the 64 that are in the list from sheet1 and compare them manually to the list of 64 to see what is not within that list

I do not care what the sheet names are

Does this make better sense? Thank you for all your patience and valuable time.

**Larbec** · 12-06-2015, 03:14 PM

Here are snips of the file i attached. Keep in mind this is "not" what i am looking for. This is an example of how my sheets are set up and another type of sorting I am doing.

Attachment 14898 Attachment 14899 Attachment 14900

**SamT** · 12-06-2015, 04:04 PM

Do I change something in here?

Sub InitializeDictionary(ShtName As String)
Dim Rw As Long
Dim i As Long
Dim KeyName
'.Columns(3) 'edit Sheet name to suit
For Rw = 4 To 2004
For i = 3 To 37
KeyName = KeyName & .Cells(Rw, i)
Next i
If Len(KeyName) <> 6 Then MsgBox "Row " & Rw & " Combo is " & KeyName
If Not UsedVariations.Exists(KeyName) Then UsedVariations.Add KeyName, "Used"
KeyName = ""
Next Rw
End With
End Sub

I really am trying To learn And I appreciate your patience

Sorry. A minor, but fatal error on my part that I didn't see because I wrote the code in a Sheet module even though it belongs in a standard module,

Please replace

'.Columns(3) 'edit Sheet name to suit

With

With Sheets(ShtName)

At this point in time, this reply to your post #6, my code has two objects( Combinations Collection, and UsedVariations Dictionary.) The Combinations Collection contains every possible combination of EO and UD. The dictionary contains every used combination on the Sheet passed to it via "ShtName As String", as shown in the Sub Demo.

The three subs I gave you show how to create a list of combinations of EO and UD and EOUD and showed you how to compare all used combinations one one sheet to all possible combinations of EOUD.

You said to Paul,

What about the other part I was asking for?

"Once I can figure that out all the possible combinations I would like to have a Macro that can look through a list of say 2000 rows x 6 columns and find out which combinations are not in the list yet of possible combinations, and either high light them or extract them to a different sheet. 1 Macro will do the EEEEEE OOOOOO and the other will do the UUUUUU DDDDDD. If not this way then perhaps pull out all the combinations that are in the 2001 rows and I can manually compare them to those in the list of "all" the possible combinations."

Does this make sense? I basically want to be able to view (look at) all the different permutations that each letter group can make (64 permutations) (see it on a spreadsheet) and then go through the 2000 rows and compare it to the list and see what is absent or not in that list of 64.

You have also said

I really am trying To learn And I appreciate your patience

It is time for you to start trying to write the code to do what you want it to do. Putting the result on a sheet means replacing MsgBox with Range =

**Larbec** · 12-06-2015, 04:40 PM

Thanks Sam, I'll try my best (-:

**Larbec** · 12-06-2015, 05:09 PM

@snb

Thanks snb!! Runs perfectly!!!!

**snb** · 12-07-2015, 01:14 AM

If your data are in K1:K2000, you can check the existence of any of the permutations in the range K1:K2000 using:

Sub M_snb()
    with sheet1
       .Cells(1).Resize(64) = [index(substitute(substitute(text(dec2bin(row(1:64)-1),"'000000"),"1","E"),"0","O"),)]
       .Cells(65, 1).Resize(64) = [index(substitute(substitute(text(dec2bin(row(1:64)-1),"'000000"),"1","U"),"0","D"),)]
       .Cells(1, 2).Resize(128) = "=N(not(iserror(match(A1,K$1:K$2000,0))))"
    end with
End Sub

NB. Adapt K1:K2000 to your requirements.

Thread: Looking for some help to find "missing" letter groups

Thread Tools

Display

Looking for some help to find "missing" letter groups

Posting Permissions