Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total capacity
of students.
On application Each candidate also supplies priorities for himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65 respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to such
problems or provide a code that I can work on.
TIA
J_J

You are probably better off doing this by hand with a little bit of
help from XL.

Suppose your original data are in B1:F13. Then, duplicate the data set
in B16:F28. Now, sort this 2nd data set on the score column
(descending). Draw a lower border for the person who matches the cut-
off scores for the individual departments. In the sample data set that
would be Martyn at 70 and Sue at 65.

In I15:K15 enter A, B, and C. In I16 enter the formula =COUNTIF($H$17:
$H$28,I15) Copy it to J16:K16. These three cells tell you how many
candidates have been assigned to each department.

In column H, starting with the first candidate (row 17) assign that
person to her/his first choice. Keep on going down the list as long as
the numbers in J16:K16 are less than the established capacity and the
candidate is above the first threshold (cell border drawn in para 2
above).

Once a department capacity is reached, start assigning people based on
their second (and later their third) choices. Once the department
minimum requirement is reached stop considering that department for all
candidates below that row.

For the provided sample set, I get:
Nancy A
Mac B
Judy A
Mag B
Karen C
John C
Judy C
Martyn A
Jon B
Bush B
Sue C
Mandy -

IMO this is not only very transparent but also the quickest and the
easiest to authenticate. Compared to it any programmatic solution (or
formula based solution) will take longer just because of the debugging
and testing requirements. In addition, it still may not be as
transparent as the above.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total capacity
of students.
On application Each candidate also supplies priorities for himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65 respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to such
problems or provide a code that I can work on.
TIA
J_J

Tushar ,
thanks for your efforts. Yes this worked well for me too but I need to
create a sort of file that may be used by anybody. The data region/size may
vary, the departments may vary and the point limits may vary.
Thus a programmatical approach is much suitable here...
Hope you can still propose an alternative
Regards
J_J



"Tushar Mehta" wrote in message
m...
You are probably better off doing this by hand with a little bit of
help from XL.

Suppose your original data are in B1:F13. Then, duplicate the data set
in B16:F28. Now, sort this 2nd data set on the score column
(descending). Draw a lower border for the person who matches the cut-
off scores for the individual departments. In the sample data set that
would be Martyn at 70 and Sue at 65.

In I15:K15 enter A, B, and C. In I16 enter the formula =COUNTIF($H$17:
$H$28,I15) Copy it to J16:K16. These three cells tell you how many
candidates have been assigned to each department.

In column H, starting with the first candidate (row 17) assign that
person to her/his first choice. Keep on going down the list as long as
the numbers in J16:K16 are less than the established capacity and the
candidate is above the first threshold (cell border drawn in para 2
above).

Once a department capacity is reached, start assigning people based on
their second (and later their third) choices. Once the department
minimum requirement is reached stop considering that department for all
candidates below that row.

For the provided sample set, I get:
Nancy A
Mac B
Judy A
Mag B
Karen C
John C
Judy C
Martyn A
Jon B
Bush B
Sue C
Mandy -

IMO this is not only very transparent but also the quickest and the
easiest to authenticate. Compared to it any programmatic solution (or
formula based solution) will take longer just because of the debugging
and testing requirements. In addition, it still may not be as
transparent as the above.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as
a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total
capacity
of students.
On application Each candidate also supplies priorities for
himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65
respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that
the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to
such
problems or provide a code that I can work on.
TIA
J_J

Fair enough about someone else needing to use the template. However,
writing a VBA-only solution requires way too much testing and debugging
compared to an XL solution. You may want to record a macro while doing
the below. The combination of the macro and the XL solution below will
automate the complete process.

Suppose your department info is in I1:K3
Dept Min Points Capacity
A 70 3
B 55 4
C 65 4

Suppose you have the list of students sorted by score (descending) in
B16:F28
Name Points 1stChoice 2ndChoice 3rdChoice
Nancy 90 A C B
Mac 89 B A C
Judy 89 A C B
Mag 86 B C A
Karen 78 C B A
John 78 C A B
Judy 77 C B A
Martyn 70 A C B
Jon 67 A B C
Bush 66 A B C
Sue 65 B A C
Mandy 56 C A B

Then, in K17 enter the formula =D17 (the first person always gets what
s/he wants).
Then in H18:J18 enter the *array* formula =(VLOOKUP(D18:F18,$I$2:$K
$4,2,FALSE)<=C18)*(COUNTIF($K$17:K17,D18:F18)<VLOO KUP(D18:F18,$I$2:$K
$4,3,FALSE))
In K18 enter =INDEX(D18:F18,,MATCH(1,H18:J18,0))
Copy H18:K18 as far down as you have data.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Tushar ,
thanks for your efforts. Yes this worked well for me too but I need to
create a sort of file that may be used by anybody. The data region/size may
vary, the departments may vary and the point limits may vary.
Thus a programmatical approach is much suitable here...
Hope you can still propose an alternative
Regards
J_J



"Tushar Mehta" wrote in message
m...
You are probably better off doing this by hand with a little bit of
help from XL.

Suppose your original data are in B1:F13. Then, duplicate the data set
in B16:F28. Now, sort this 2nd data set on the score column
(descending). Draw a lower border for the person who matches the cut-
off scores for the individual departments. In the sample data set that
would be Martyn at 70 and Sue at 65.

In I15:K15 enter A, B, and C. In I16 enter the formula =COUNTIF($H$17:
$H$28,I15) Copy it to J16:K16. These three cells tell you how many
candidates have been assigned to each department.

In column H, starting with the first candidate (row 17) assign that
person to her/his first choice. Keep on going down the list as long as
the numbers in J16:K16 are less than the established capacity and the
candidate is above the first threshold (cell border drawn in para 2
above).

Once a department capacity is reached, start assigning people based on
their second (and later their third) choices. Once the department
minimum requirement is reached stop considering that department for all
candidates below that row.

For the provided sample set, I get:
Nancy A
Mac B
Judy A
Mag B
Karen C
John C
Judy C
Martyn A
Jon B
Bush B
Sue C
Mandy -

IMO this is not only very transparent but also the quickest and the
easiest to authenticate. Compared to it any programmatic solution (or
formula based solution) will take longer just because of the debugging
and testing requirements. In addition, it still may not be as
transparent as the above.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as
a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total
capacity
of students.
On application Each candidate also supplies priorities for
himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65
respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that
the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to
such
problems or provide a code that I can work on.
TIA
J_J

J_J,

This sure sounds like a class assignment ???
In any case, this will certainly give you something to work on.
(you will probably get marked down for using the "StartOver" code)
It worked for me twice.

'Jim Cone - San Francisco, USA - March 17, 2005
'-----------------------------------------------
'Uses data in Range C5:C17
'Name Points 1stChoice 2ndChoice 3rdChoice
'Nancy 90 A C B
'etc.

'Determines names of students for each class
'and lists them in Range B20:E22
'NOTE: there are lots of ways for this to go wrong...
' The data has to be in the specified range.
' There must enough people qualified for all of the classes,
' or you can get an endless loop.
' Duplicate entries
' Others ?
'-----------------------------------------------
Sub FillClasses()
Dim arr1st() As String
Dim arr2nd() As String
Dim arr3rd() As String
Dim lngCol As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim rngCell As Excel.Range
Dim rngPointList As Excel.Range

Set rngPointList = Range("C5:C17")
lngCol = 2
ReDim arr1st(1 To 3)
ReDim arr2nd(1 To 4)
ReDim arr3rd(1 To 4)

StartOver:
For Each rngCell In rngPointList
Select Case rngCell(0, 1).Value
Case Is 69
Select Case rngCell(1, lngCol).Value
Case "A"
i = i + 1
If i < 4 Then
arr1st(i) = rngCell(1, 0).Value
End If
Case "B"
j = j + 1
If j < 5 Then
arr2nd(j) = rngCell(1, 0).Value
End If
Case "C"
k = k + 1
If k < 5 Then
arr3rd(k) = rngCell(1, 0).Value
End If
End Select

Case Is 64
Select Case rngCell(1, lngCol).Value
Case "B"
j = j + 1
If j < 5 Then
arr2nd(j) = rngCell(1, 0).Value
End If
Case "C"
k = k + 1
If k < 5 Then
arr3rd(k) = rngCell(1, 0).Value
End If
End Select

Case Is 54
Select Case rngCell(1, lngCol).Value
Case "C"
k = k + 1
If k < 5 Then
arr3rd(k) = rngCell(1, 0).Value
End If
End Select

End Select
Next 'rngcell

'Do it again if all arrays are not filled.
If i < 3 Or j < 4 Or k < 4 Then
lngCol = lngCol + 1
GoTo StartOver
End If

Range("B20:D20").Value = arr1st()
Range("B21:E21").Value = arr2nd()
Range("B22:E22").Value = arr3rd()
Range("A20").Value = "A"
Range("A21").Value = "B"
Range("A22").Value = "C"

Set rngCell = Nothing
Set rngPointList = Nothing
End Sub
'-------------------------------------------------




"J_J" wrote in message
...
Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total capacity
of students.
On application Each candidate also supplies priorities for himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A
\ Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65 respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to such
problems or provide a code that I can work on.
TIA
J_J

J_J,

Corrections...

'Uses data in Range C5:C17
' Should be...
'Uses data in Range B5:F17

'Select Case rngCell(0, 1).Value
' Should be
'Select Case rngCell.Value

Regards,
Jim Cone
San Francisco, USA

Thank you Jim and thank you Tushar,
I'll try your suggestions and get back here for the results...
Regards
J_J


"Jim Cone" wrote in message
...
J_J,

Corrections...

'Uses data in Range C5:C17
' Should be...
'Uses data in Range B5:F17

'Select Case rngCell(0, 1).Value
' Should be
'Select Case rngCell.Value

Regards,
Jim Cone
San Francisco, USA

Hi Jim,
Thank you for your interest. Although made the corrections somehow I am not
able to get any logical results from your code. Maybe I am using a different
region or set then the example you have provided. Can you supply a working
example of a similar situation?.
Regards
J_J



"Jim Cone" wrote in message
...
J_J,

Corrections...

'Uses data in Range C5:C17
' Should be...
'Uses data in Range B5:F17

'Select Case rngCell(0, 1).Value
' Should be
'Select Case rngCell.Value

Regards,
Jim Cone
San Francisco, USA

J_J,

The point values for B and C were reversed.
Also, the code needed a check to determine if
a name had been assigned.
Since there are two Judys, I renamed the last one to Judy2.
You did say you wanted code you could work on <g.

I get the following results:
'-------------------------------------------------------
A Martyn Nancy Judy
B Sue Mac Mag Jon
C Karen John Judy2 Bush

'-------------------------------------------------------


See how this works...

'-----------------------------------------------'
Jim Cone - San Francisco, USA - March 17, 2005
' Modified on March 18, 2005
'-----------------------------------------------
'Uses data in Range B5:F17
'Name Points 1stChoice 2ndChoice 3rdChoice
'Nancy 90 A C B
'etc.

'Determines names of students for each class
'and lists them in Range B20:E22
'NOTE: there are lots of ways for this to go wrong...
' The data has to be in the specified range.
' There must enough people qualified for all of the classes,
' or you can get an endless loop.
' Duplicate entries
' Others ?
'-----------------------------------------------
Sub FillClasses()
Dim arr1st() As String
Dim arr2nd() As String
Dim arr3rd() As String
Dim lngCol As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim rngCell As Excel.Range
Dim rngPointList As Excel.Range

Set rngPointList = Range("C5:C17")
lngCol = 2
ReDim arr1st(1 To 3)
ReDim arr2nd(1 To 4)
ReDim arr3rd(1 To 4)
'These cells will be cleared as each name is assigned.
Range("A6:A17").Value = "X"

StartOver:
For Each rngCell In rngPointList
Select Case rngCell.Value

Case Is 69
Select Case rngCell(1, lngCol).Value
Case "A"
If i < 3 Then
If Len(rngCell(1, -1)) Then
i = i + 1
arr1st(i) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "B"
If j < 4 Then
If Len(rngCell(1, -1)) Then
j = j + 1
arr2nd(j) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "C"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select

Case Is 64
Select Case rngCell(1, lngCol).Value
Case "B"
If j < 4 Then
If Len(rngCell(1, -1)) Then
j = j + 1
arr2nd(j) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "C"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select

Case Is 54
Select Case rngCell(1, lngCol).Value
Case "B"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select
End Select
Next 'rngcell

'Do it again if all arrays are not filled.
If i < 3 Or j < 4 Or k < 4 Then
lngCol = lngCol + 1
GoTo StartOver
End If

Range("B20:D20").Value = arr1st()
Range("B21:E21").Value = arr2nd()
Range("B22:E22").Value = arr3rd()
Range("A20").Value = "A"
Range("A21").Value = "B"
Range("A22").Value = "C"

Set rngCell = Nothing
Set rngPointList = Nothing
End Sub
'-------------------------------------------------



"J_J" wrote in message
...
Hi Jim,
Thank you for your interest. Although made the corrections somehow I am not
able to get any logical results from your code. Maybe I am using a different
region or set then the example you have provided. Can you supply a working
example of a similar situation?.
Regards
J_J

Jim,

Thank you so much for your code.
I have the chance to try it with a much "bigger" data set and compare the
results with the "manual" method Tushar suggested in the weekend...
As you have made it clear in your previous posts, there are lots of
occasions that the code tends to calculate "wrong" Dept. names compared to
Tushar's. Records having the same points, records with same names or blank
Dept. Names, records with higher points then the ones with 2nd choice but
having the same dept as their 3thcoice...etc introduce trouble.

One thing for sure. If I sort the list before executing the macro then the
results with the "manual method" and "VBA code method" are the same.
So I am using the code like this now:
1. Sorting the whole data with ref. to the Points column "descending".
2. Execute your VBA code.
Sincerely
J_J



"Jim Cone" wrote in message
...
J_J,

The point values for B and C were reversed.
Also, the code needed a check to determine if
a name had been assigned.
Since there are two Judys, I renamed the last one to Judy2.
You did say you wanted code you could work on <g.

I get the following results:
'-------------------------------------------------------
A Martyn Nancy Judy
B Sue Mac Mag Jon
C Karen John Judy2 Bush

'-------------------------------------------------------


See how this works...

'-----------------------------------------------'
Jim Cone - San Francisco, USA - March 17, 2005
' Modified on March 18, 2005
'-----------------------------------------------
'Uses data in Range B5:F17
'Name Points 1stChoice 2ndChoice 3rdChoice
'Nancy 90 A C B
'etc.

'Determines names of students for each class
'and lists them in Range B20:E22
'NOTE: there are lots of ways for this to go wrong...
' The data has to be in the specified range.
' There must enough people qualified for all of the classes,
' or you can get an endless loop.
' Duplicate entries
' Others ?
'-----------------------------------------------
Sub FillClasses()
Dim arr1st() As String
Dim arr2nd() As String
Dim arr3rd() As String
Dim lngCol As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim rngCell As Excel.Range
Dim rngPointList As Excel.Range

Set rngPointList = Range("C5:C17")
lngCol = 2
ReDim arr1st(1 To 3)
ReDim arr2nd(1 To 4)
ReDim arr3rd(1 To 4)
'These cells will be cleared as each name is assigned.
Range("A6:A17").Value = "X"

StartOver:
For Each rngCell In rngPointList
Select Case rngCell.Value

Case Is 69
Select Case rngCell(1, lngCol).Value
Case "A"
If i < 3 Then
If Len(rngCell(1, -1)) Then
i = i + 1
arr1st(i) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "B"
If j < 4 Then
If Len(rngCell(1, -1)) Then
j = j + 1
arr2nd(j) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "C"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select

Case Is 64
Select Case rngCell(1, lngCol).Value
Case "B"
If j < 4 Then
If Len(rngCell(1, -1)) Then
j = j + 1
arr2nd(j) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
Case "C"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select

Case Is 54
Select Case rngCell(1, lngCol).Value
Case "B"
If k < 4 Then
If Len(rngCell(1, -1)) Then
k = k + 1
arr3rd(k) = rngCell(1, 0).Value
rngCell(1, -1).ClearContents
End If
End If
End Select
End Select
Next 'rngcell

'Do it again if all arrays are not filled.
If i < 3 Or j < 4 Or k < 4 Then
lngCol = lngCol + 1
GoTo StartOver
End If

Range("B20:D20").Value = arr1st()
Range("B21:E21").Value = arr2nd()
Range("B22:E22").Value = arr3rd()
Range("A20").Value = "A"
Range("A21").Value = "B"
Range("A22").Value = "C"

Set rngCell = Nothing
Set rngPointList = Nothing
End Sub
'-------------------------------------------------



"J_J" wrote in message
...
Hi Jim,
Thank you for your interest. Although made the corrections somehow I am
not
able to get any logical results from your code. Maybe I am using a
different
region or set then the example you have provided. Can you supply a
working
example of a similar situation?.
Regards
J_J