PDA

View Full Version : Recursion Help



Oh4Sh0
11-20-2008, 03:17 PM
I'm trying to create a program to help a friend who owns a camping site. He has 30 sites, and each of the site gets "12 choices" as to which boat tie-up they'd like. The priority is based on the campsite, so spot 1 gets 1st priority and so on.

I'm trying to come up with the best recursive process for computing the results. Assuming I've got all the data on a single spreadsheet (Each site with their preferences), can anyone provide some support on a good method of recursion so I'm not writing thousands of lines of code?

I.e. I'd have something like

Where Priority = 1
Dock = Preference1
Used1 = Preference1
Where Priority = 2
If Preference1 != Used1 Then
Dock = Prefernce1
Else
Dock = Preference2
Used2 = Preference2

Sorry, that's pretty rough.. been several years (vb6) since I've done hardly any programming. Appreciate any help.

Thanks

Bob Phillips
11-20-2008, 03:59 PM
We would need to see an example of the data to work it through, otherwise all we can do is ome sort of pseudo-code such as you have done.

Oh4Sh0
11-20-2008, 04:32 PM
See link (add http prefix, post count !>5)
s103.photobucket.com/albums/m125/sillyboi_2006/?action=view&current=Proj.jpg


What's on the right side is the data gathered about the member through a series of linked spreadsheets. The "Priority" is calculated through a series of if statements. So as you can see it's a little more complicated then "site 1", "site 2", etc, but I've got that taken care of.

What's on the left is my finalized display page. I need to find a method of recursion that will take the "Preference" of each user, and assign them based on priority. So, as per the picture, "Bob" with a priority of 9, couldn't have one of his first priorities if they were already taken by another member.

Hope it's clear, I get confused just trying to think about it. :) The IF statement was fun to make though.

Bob Phillips
11-20-2008, 05:54 PM
Doesn't Bob get first pick as he is the first in the list?

What does all the Pref stuff under Bob on the right do, how does it relate to this?

That workbook would help us enormously.

Oh4Sh0
11-20-2008, 06:51 PM
No, the order in the list is not relevant. Bob gets 9th pick based on his address (seniority is based on address) which is a combination of his building and unit number. The "pref" stuff is the user's (in this case Bob's) 12 preferences of boat "docks" (Where 1 is the most preferred spot, and 12 is the least preferred spot). I had said there are 30 sites (to keep it simpler and low), but there are actually 50. So since Bob is "9th" in Priority, that means he gets "9th" pick in boat dock. His first prefence then would be his boat dock assignment if nobody before him chose it. If they did, then the recursion would have to check each of his preferences from 1st on to see if someone with a higher priority rating had already chose them. I'll upload the workbooks shortly.

Oh4Sh0
11-20-2008, 07:35 PM
You can grap them at
tdalliance.org/kc/Dock.zip (case sensitive)

You must save them to C:\Shipslip to work properly (linking)
There are two main workbooks -
Master and MasterUser.
MasterUser is the form the owner of the site will send to his 50 members. When he gets them back he'll rename them User01, User02, etc. as you see from my four example files in there. What he renames them to will not reflect their priority and will have no bearing on the members at all - it will simply be the order he recieves them back in. Hence then the "Data" worksheet of the Master book can be in any order in terms of priority - because the first user in the list could have any priority value - he's just the first one who responded.

The 'Master' workbook then collects the data and determines the Priority based on the Building + the Unit (it's a bit of a weird addressing scheme). I need to take these users+priorities and find a recursive method of assigning the User with the highest priority his 1st preference in Dock, the 2nd user his 1st preference as well (if not taken by the first user), and if it is taken, it then needs to assign him his second preference. (Users may have up to 12 preferences - so if say the 50th users 12 preferences are already taken, then I will have it print something out such as "Preferences Invalid - No solution". or something along those lines.

Thanks for taking a look xld. Definitely appreciate any help I can get.

Oh4Sh0
11-22-2008, 03:57 PM
No such luck?

Oh4Sh0
12-01-2008, 04:59 PM
Well I arrived at a solution with the aid of a friend, however I'm encountering one small issue. Essentially I want to compare the value of a cell and make sure it does not exist in a column before I add it to a column.
However, my statement below throws a type mismatch error because of the range R2:R52.

ElseIf Worksheets("Data").Range("E" & intRowStep).Value = intPriority And Worksheets("Data").Range("G" & intRowStep) <> Worksheets("Data").Range("R2:R52") Then

I can get around this by ANDing individual cells together such as:

If Worksheets("Data").Range("E" & intRowStep).Value = intPriority And Worksheets("Data").Range("F" & intRowStep) <> Worksheets("Data").Range("R2") And Worksheets("Data").Range("F" & intRowStep) <> Worksheets("Data").Range("R3") Then

but obviously I would not like to do that for 50 cells. Can anyone explain what I'm missing here?

To make it simpler to understand - all I'm wanting to do is check that a value within a given cell does not exist in a column. If it does, then it branches to the next ElseIf statement.
Thanks guys.