Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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?

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.

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

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

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.

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.

Related Topics:

- Testing Models with CakePHP 1.2 test suite
- Need testing tools
- Load testing with JRun4.0
- Anyone know Java Grinder Load Test?
- How can i test my midlet on emulator?
- Testing a QueryString
- How to test what key was pressed?
- Testing for the Named Range of Target
- How to test what key was pressed?
- Software Testing Process
- Web Application Load Testing
- Test Driven Development
- Web Application Testing Tool
- Test Case Management
- test computer download speed
- tomcat load test
- software testing
- testing my system
- Test educational JAVA applet
- Testing programs with Wintask
- floating test on applet
- Test JApplet from hard drive through html
- Testing Compatibility of Java Applet
- question was asked in aptitude test
- help with testing sort algorithms