PDA

View Full Version : 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.


14887

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

14889

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.

14890

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


14894

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


14895

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.

148981489914900

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 patienceIt 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.

Paul_Hossler
12-07-2015, 08:03 AM
@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
12-07-2015, 08:54 AM
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
12-07-2015, 09:23 AM
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
12-07-2015, 01:21 PM
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
12-07-2015, 03:05 PM
I think 'VBA for Dummies' (author John Walkenbach) could be a good start.

Did you use my code in http://www.vbaexpress.com/forum/showthread.php?54461-Looking-for-some-help-to-find-quot-missing-quot-letter-groups&p=334745&viewfull=1#post334745, because it solves your 'problem' for 100%.

Larbec
12-07-2015, 04:38 PM
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
12-08-2015, 01:08 AM
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
12-08-2015, 05:18 AM
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

14923

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
12-08-2015, 05:26 AM
If you use column AN to concatenate all values in column A to AK you can adapt my macro to do the checking.

Larbec
12-08-2015, 05:52 AM
Gotcha, I will try that thanks snb!!!!

SamT
12-08-2015, 01:49 PM
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 :D