Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Succesive Tests

  Asked By: Qadriyah    Date: Dec 16    Category: MS Office    Views: 811
  


*Trying to find the least number of sets to complete a task.
I have a list of 3 x/y/z values with 2 values of z each.
The possible values are: (examples)
1,3,5 and 1,3,6 (#1 and #2)
1,6,7 and 1,6,9 (#3 and #4))
4,4,5 and 4,4 8 (#5 and #6)
Needed is a mechanism to test #1, then #2.
Then test #1#3, then #1#4, then #2#3, then #2#4,
Then 135 then 136 then 145 then 146 then 235 then 236 then 245 then 246
If I wanted to test all combinations, it would be easy.
But I want to start small and expand the test cases only if needed (in
interest of time)
My brain can't seem to come up with the right algorithm to accomplish
the task
Finally I want to expand to 25 sets so a manual expansion isn't really
feasible.
Another way to look at this is to think of sets.
1. Test 1st set of data (2 sequential test cases)
2. If fail, test 1st and 2nd sets together (4 sequential **test **cases)
3. If fail test 1st 2nd and 3rd sets together (8 sequential **test **cases)
Next has 16 cases,
Next has 32 cases
etc etc etc.
Sort of confusing, but I hope someone understands.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Adelgiese Fischer     Answered On: Dec 16

It sounds like you need either a series of nested loops (For..Next), a recursive
routine, or a combination of iteration & recursion. I've written a few routines
like this before and am glad to help if I can. When you test  a set of numbers,
what is the test? What determines if the set passes or fails?

 
Answer #2    Answered By: Oliver Jones     Answered On: Dec 16

Through deduction, I've created a matrix of points that only have two
possibilities (most have more).
So I'm cycling through the "two" matrix in successive levels of complexity.
I mean try the first (2 cases), then the 1st and 2nd (4 cases), then the
1st and 2nd and 3rd (8 cases), etc.
I agree about loops and/or recursion - but couldn't come up with an
algorithm.

 
Answer #3    Answered By: Devin Ross     Answered On: Dec 16

See if this code does (more or less) what you want. I entered the 25 sets as
3-digit numbers on a sheet in column A starting in row 1 (they are loaded into
an array of a user-defined structure, and it should be pretty easy to modify
reading a different layout into the array). Since I don't know exactly what your
test is doing, I put the test  in a separate function (EvaluateSets), just
looking for the first two 3-digit numbers that add to a target sum (change the
function call to the real test you want. Return True or False).

Option Explicit

'Global variables for Hawk's problem
Public Type SetInfo
SetNbr As Integer 'Set number  (assigned sequentially by macro)
Val1 As Integer 'First value in set
Val2 As Integer 'Second value in set
Val3 As Integer 'Third value in set
Addr As String 'Address of cell
End Type
Public Cellz() As SetInfo, SetCnt As Integer
Public SolnSet1 As Integer, SolnSet2 As Integer

Sub FindSoln()
Dim x As Integer, aa As Integer
'Assuming data starts in A1 on the active sheet. Sets values  are concatenated
into 3-digit numbers.
ActiveSheet.Range("A1").Activate
x% = 1
SetCnt% = -1
Do While Len(ActiveCell.Value) > 0
SetCnt% = SetCnt% + 1
ReDim Preserve Cellz(SetCnt%)
Cellz(SetCnt%).SetNbr = x%
Cellz(SetCnt%).Val1 = Left(ActiveCell.Value, 1)
Cellz(SetCnt%).Val2 = Mid(ActiveCell.Value, 2, 1)
Cellz(SetCnt%).Val3 = Right(ActiveCell.Value, 1)
Cellz(SetCnt%).Addr = ActiveCell.Address
x% = x% + 1
ActiveCell.Offset(1, 0).Activate
Loop
'Start an iterative loop through all the sets, from which we will call TestSet()
to begin the recursive chain.
'If TRUE is returned, we found a solution!
For aa% = 0 To (SetCnt% - 1)
If TestSet(aa%, aa% + 1) = True Then
MsgBox "A solution was found."
MsgBox Cellz(SolnSet1).Addr & " = " & Range(Cellz(SolnSet1).Addr)
MsgBox Cellz(SolnSet2).Addr & " = " & Range(Cellz(SolnSet2).Addr)
Exit Sub
End If
Next aa%
MsgBox "No solution was found."
ReDim Cellz(0)
End Sub

Public Function TestSet(Set1 As Integer, Set2 As Integer) As Boolean
'Passes the values in Set1 and Set2 to be tested in another function. If test
succeeds,
'save as SolnSet1 and SolnSet2, then return TRUE. If test fails, keep Set1 the
same
'and make the next set Set2. Call TestSet again with these parameters.
If EvaluateSets(Set1, Set2) = True Then
SolnSet1% = Set1
SolnSet2% = Set2
'Make the cells with the solution sets BOLD.
Range(Cellz(SolnSet1).Addr).Font.Bold = True
Range(Cellz(SolnSet2).Addr).Font.Bold = True
TestSet = True
Exit Function
Else
If Set2 + 1 > SetCnt% Then
TestSet = False
Exit Function
ElseIf TestSet(Set1, Set2 + 1) = True Then
TestSet = True
Exit Function
End If
End If
TestSet = False
End Function

Public Function EvaluateSets(SetA As Integer, SetB As Integer) As Boolean
'Do some test on the two sets to determine if they are the right two. I'm just
'adding the two 3-digit numbers to see if they equal a certain sum.
If (Int(Cellz(SetA).Val1 & Cellz(SetA).Val2 & Cellz(SetA).Val3) + _
Int(Cellz(SetB).Val1 & Cellz(SetB).Val2 & Cellz(SetB).Val3)) = 1334 Then
EvaluateSets = True
Exit Function
Else
EvaluateSets = False
Exit Function
End If
End Function

 
Answer #4    Answered By: Aran Boonliang     Answered On: Dec 16

It looks viable, so will try it out this weekend when I have time.
Another suggestion was to have an array of subscripts with values  0-2,
sort of like indirect referencing.
Then can cycle through the array of subscripts using trinary counting
(each variable has 1st, 2nd and don't use)
The while thing is about 6 lines long, but don't know if it will work yet.
Again, thanks for your input ...

 
Answer #5    Answered By: Vickie Smith     Answered On: Dec 16

It occurred to me that if you are testing two sets at a time from a total of
25 sets, that's only about 300 tests at most. You don't need a complicated
recursive routine for that, just a couple of nested iterative loops. So, I
rewrote the code I sent you yesterday as follows:

Option Explicit

'Global variables for WarHawk's problem
Public Type SetInfo
SetNbr As Integer 'Set number  (assigned sequentially by macro)
Val1 As Integer 'First value in set
Val2 As Integer 'Second value in set
Val3 As Integer 'Third value in set
Addr As String 'Address of cell
End Type
Public Cellz() As SetInfo, SetCnt As Integer

Sub FindSoln()
Dim x As Integer, aa As Integer, bb As Integer
'Assuming data starts in A1 on the active sheet. Sets values  are concatenated
into 3-digit numbers.
ActiveSheet.Range("A1").Activate
x% = 1
SetCnt% = -1
Do While Len(ActiveCell.Value) > 0
SetCnt% = SetCnt% + 1
ReDim Preserve Cellz(SetCnt%)
Cellz(SetCnt%).SetNbr = x%
Cellz(SetCnt%).Val1 = Left(ActiveCell.Value, 1)
Cellz(SetCnt%).Val2 = Mid(ActiveCell.Value, 2, 1)
Cellz(SetCnt%).Val3 = Right(ActiveCell.Value, 1)
Cellz(SetCnt%).Addr = ActiveCell.Address
x% = x% + 1
ActiveCell.Offset(1, 0).Activate
Loop
'Start iterative loops through all the sets.
For aa% = 0 To (SetCnt% - 1)
For bb% = (aa% + 1) To SetCnt%
If TestSets(aa%, bb%) = True Then
MsgBox "A solution was found: " & Cellz(aa%).Addr & "=" &
Range(Cellz(aa%).Addr) & _
" and " & Cellz(bb%).Addr & "=" &
Range(Cellz(bb%).Addr)
Exit Sub
End If
Next bb%
Next aa%
MsgBox "No solution was found."
ReDim Cellz(0)
End Sub

Public Function TestSets(SetA As Integer, SetB As Integer) As Boolean
'Do some test  on the two sets to determine if they are the right two. I'm just
'adding the two 3-digit numbers to see if they equal a certain sum.
If (Int(Cellz(SetA).Val1 & Cellz(SetA).Val2 & Cellz(SetA).Val3) + _
Int(Cellz(SetB).Val1 & Cellz(SetB).Val2 & Cellz(SetB).Val3)) = 614 Then
TestSets = True
Exit Function
Else
TestSets = False
Exit Function
End If
End Function

If you get the other approach (array of subscripts, trinary counting) working, I
would love to see it.

 
Answer #6    Answered By: Lois Schmidt     Answered On: Dec 16

I just didn't explain very well ...
I have 10 variables, each with 3 possible values  (#0, #1 and #2).
The #3 value is just a value to exclude from the combinations.
It's the same as counting in base 3.
If I had 10 variables, the number  of cases would be 3**10.
So the test  sequence goes like this for 3 variables:
000
001
002
010
011
012
020
021
022
100
101
102
110
111
112
120
121
122
200
201
202
210
211
212
220
221
222
More clear? (I hope)

 
Didn't find what you were looking for? Find more on Succesive Tests Or get search suggestion and latest updates.




Tagged: