Looking for some help to find "missing" letter groups

[Paul_Hossler]

@Larbec

@snb

Thanks snb!! Runs perfectly!!!!

As a learning exercise, make sure you can explain to yourself and understand what each piece is doing

THAT's the way to learn

(PS I couldn't explain it because I'm not a WS formula guru like snb. I go for the long, wordy code style)

[Larbec]

Thanks Paul, I'm really struggling with writing code. Our local college does not offer any courses and to be honest with you I do not know where to begin besides reading forums. Even though I'm a programmer (relay logic, PLC and Robotics) it takes me several times before the light bulb turns on.

im still beating my head against the wall to get what you wrote and make it sort what I need. What a Snb provided gave me all the possible combinations and it was do simple for me to just put it in a module a get it to run .... I'll get there someday I'm sure Thanks again

[snb]

Once you realize that the numbers from 000000 to 999999 contain all permutations of the numbers 0 to 9 in groups of 6 numbers, you can can replace numbers by characters like A,B,C, etc.

All possible permutations of 2 items (isn't that binary) in 6 character combinations (2^6) is identical to the binary code from 000000 to 111111.
If we replace in the numbers 000000 to 111111 the zeros by 'E" and the 1's by "O" we have all permutations of E & O in strings with 6 characters.

Instead of a course I'd advice a book. That has at least a structure from simple to complex, an index, and references to other topics that will be discussed later.

[Larbec]

Thanks Snb. It would be nice to find what I call a quick sheet that has the most widely used commands like what is Dim and how do you apply it I'm am example. What do the numbers indicate and letters like i and r d for example. I've downloaded an excel book but it's very complexed. I'll look around on this forum and see what's available

do you know what a good book would be?

[snb]

I think 'VBA for Dummies' (author John Walkenbach) could be a good start.

Did you use my code in http://www.vbaexpress.com/forum/show...l=1#post334745, because it solves your 'problem' for 100%.

[Larbec]

Thanks snb I'll download the book and get aboard copy

I did see the post and Macro but won't be able to try it tonight. My sheets are set up into several columns and the letters spread out for shading and filtering purposes. To run this correctly I need to place all the E's O's and Us Ds into 1 column? Correct?

[snb]

In that case you need another formula:

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(countif(" & .UsedRange.Offset(, 2).Address & ",A1)>0)"
    End With
End Sub

[Larbec]

snb,

Thank you for your hard work but I am not sure Ive been clear about what I am looking for I am missing what you have provided. I m looking for the missing permutations from my group of EOs and UDs of possible total permutations. Unless I should go through what you provided and compare to my list manually? Maybe this snip will clear it up

Missing from Permutations.jpg

For some reason I can not still add anymore reputations. Its as if I have to add it to another that has not helped and then come back and add to yours

[snb]

If you use column AN to concatenate all values in column A to AK you can adapt my macro to do the checking.

[Larbec]

Gotcha, I will try that thanks snb!!!!

[SamT]

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

To make that sub put a list of the unused combinations on a worksheet Add
Dim rw as long
rw = 1

And change the MSgBox line to
Sheets(your sheet name).Cells(rw,"a") = References(i)
rw = rw + 1

I leave it to you to figure out how to split the References collection into two collections (EOs and DUs.) I have preciously shown you how to create a dictionary of UsedVariations of just one sheet's usage of U's and D's

I would write more, but I think I have to go to the hospital soon. Carry on. and don't be ashamed to ask stupid sounding questions. we all have had them, that's how we now know the answers