Hi, I could use some assistance with code that makes
assignments based on a priority in one group with a
ranking in another group.

I have master 2 lists, people and items.
The people contains: Name, experience, priority1,
priority2, priority3.
The items list contains: Name, ranking.

On a separate daily task worksheet assignments need to be
made to the items that are available with the people that
are available. The items available and people available
can be different each day.

The items can be in one or more of the three priorities,
and the items ranking may not be unique.

However, as soon the logic is hit, don't consider that
person or item again.

The order for assignments is, check priority1 for all
people available, then priority2, then priority3. If the
item is found under a persons priority, assign the item to
them on the daily task sheet and do not consider them for
any other items, or that item for any other person.

If the item is not found in one of the 3 priorities,
assign the highest ranked item (1 is highest) to the most
experienced person (highest number is the most). Don't
consider that person or item again.

If the item is still not assigned, return unassigned.

e.g.
People:
Name=Name1
Experience=6
priority1= Carrots
priority2=Grapes
priority3=Grapefruit

Name=Name2
Experience=5
priority1= Squash
priority2=Apples
priority3=Bananas

Name=Name3
Experience=6
priority1= Apples
priority2=Squash
priority3= Tomatoes

Items
Name=Apples
Ranking=1

Name=Grapes
Ranking=1

Name=Oranges
Ranking=3

Name=Tomatoes
Ranking=2

Name=Watermelon
Ranking=6

Using the above people and items if apples, tomatoes,
oranges and watermelon are available
Apples are assigned to Name3. (Found first it priority1)
Tomatoes are assigned to Name1 (most experiences, highest
ranking)
Oranges are assigned to Name2 (next most experienced, next
highest ranking)
Watermelon is "unassigned"

Thanks, if you are sill here.

Hi, this could use soem polishing up to give it a little
more flexibility but here is a good start.

I set up a spreadsheet as follows:

Name1,2,... in cells b3 thru f3 (allowed)
with exp, pri1,pri2, and pri3 in rows 4 thru 7

items in cells b9 thru g9 (allowed)
with rank in row 10

Items to be assigned in b13 thru e13
with rank in row 14 via hlookup(b13,b9:f10,2,false)
and row 15 left blank for assigning names

ranges with counts of names, items, and toassign

John

Given all that, this seemed to work:

Sub Macro1()
'
' Macro1 Macro
'
' Names,Items, Toassign are the counts of # Names, #
Items, and
' # of Items to assign to someone
'
Dim Names As Integer
Dim Items As Integer
Dim Toassign As Integer
Dim ID(25) As String
Dim Exp(25) As Integer
Dim Pri1(25) As String
Dim Pri2(25) As String
Dim Pri3(25) As String
Dim Item(25) As String
Dim Rank(25) As Integer
Dim IDassign(25) As String
'Sort Names by Experience
'
Range("B3:G7").Select
Selection.Sort Key1:=Range("B4"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
'
'Sort Items by Rank
'
Range("B9:G10").Select
Selection.Sort Key1:=Range("B10"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
'
' Read in Names Count and Items Count
'
Names = Range("Names").Value
Items = Range("Items").Value
Toassign = Range("toassign").Value
'
' Read in Information
'
For i = 1 To Names
ID(i) = Cells(3, i + 1).Value
Exp(i) = Cells(4, i + 1).Value
Pri1(i) = Cells(5, i + 1).Value
Pri2(i) = Cells(6, i + 1).Value
Pri2(i) = Cells(7, i + 1).Value
Next i
For i = 1 To Toassign
Item(i) = Cells(13, i + 1).Value
Rank(i) = Cells(14, i + 1).Value
Next i
'
'Search items for a match under ID's
'
For i = 1 To Toassign
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj
If Item(i) = Pri1(j) Then GoTo found
nextj:
Next j
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj2
If Item(i) = Pri2(j) Then GoTo found
nextj2:
Next j
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj3
If Item(i) = Pri3(j) Then GoTo found
nextj3:
Next j
GoTo nexti
found:
IDassign(i) = ID(j)
'Change Name of assigned name to NA
ID(j) = NA
'Change Name of assigned Item to Assigned
Item(i) = Assigned
nexti:
Next i
'If item not found, assign highest ranked item to most
'experienced person available
For i = 1 To Items
' Check to see if item assigned
If Item(i) = Assigned Then GoTo nexti2
For j = 1 To Names
' Check and see if name available
If ID(j) = NA Then GoTo nextj4
'Assign Name to item i
IDassign(i) = ID(j)
'Change Name of assigned name to NA
ID(j) = NA
'Change Name of assigned Item to Assigned
Item(i) = Assigned
GoTo nexti2
nextj4:
Next j
nexti2:
If j = Names Then GoTo allassigned
Next i
allassigned:
' Check to see if any Items not assigned, and if so ID
' them as "Unassigned"
For i = 1 To Toassign
If Item(i) = Assigned Then GoTo nexti3
IDassign(i) = "Unassigned"
nexti3:
Next i
For i = 1 To Toassign
Cells(15, i + 1).Value = IDassign(i)
Next i
End Sub



-----Original Message-----
Hi, I could use some assistance with code that makes
assignments based on a priority in one group with a
ranking in another group.

I have master 2 lists, people and items.
The people contains: Name, experience, priority1,
priority2, priority3.
The items list contains: Name, ranking.

On a separate daily task worksheet assignments need to be
made to the items that are available with the people that
are available. The items available and people available
can be different each day.

The items can be in one or more of the three priorities,
and the items ranking may not be unique.

However, as soon the logic is hit, don't consider that
person or item again.

The order for assignments is, check priority1 for all
people available, then priority2, then priority3. If the
item is found under a persons priority, assign the item
to
them on the daily task sheet and do not consider them for
any other items, or that item for any other person.

If the item is not found in one of the 3 priorities,
assign the highest ranked item (1 is highest) to the most
experienced person (highest number is the most). Don't
consider that person or item again.

If the item is still not assigned, return unassigned.

e.g.
People:
Name=Name1
Experience=6
priority1= Carrots
priority2=Grapes
priority3=Grapefruit

Name=Name2
Experience=5
priority1= Squash
priority2=Apples
priority3=Bananas

Name=Name3
Experience=6
priority1= Apples
priority2=Squash
priority3= Tomatoes

Items
Name=Apples
Ranking=1

Name=Grapes
Ranking=1

Name=Oranges
Ranking=3

Name=Tomatoes
Ranking=2

Name=Watermelon
Ranking=6

Using the above people and items if apples, tomatoes,
oranges and watermelon are available
Apples are assigned to Name3. (Found first it priority1)
Tomatoes are assigned to Name1 (most experiences, highest
ranking)
Oranges are assigned to Name2 (next most experienced,
next
highest ranking)
Watermelon is "unassigned"

Thanks, if you are sill here.

.

Note that the 2nd read in of Pri2 should be pri3 - copied
too quickly - i tested some other scenarios and found that
error.


John



-----Original Message-----
Hi, this could use soem polishing up to give it a little
more flexibility but here is a good start.

I set up a spreadsheet as follows:

Name1,2,... in cells b3 thru f3 (allowed)
with exp, pri1,pri2, and pri3 in rows 4 thru 7

items in cells b9 thru g9 (allowed)
with rank in row 10

Items to be assigned in b13 thru e13
with rank in row 14 via hlookup(b13,b9:f10,2,false)
and row 15 left blank for assigning names

ranges with counts of names, items, and toassign

John

Given all that, this seemed to work:

Sub Macro1()
'
' Macro1 Macro
'
' Names,Items, Toassign are the counts of # Names, #
Items, and
' # of Items to assign to someone
'
Dim Names As Integer
Dim Items As Integer
Dim Toassign As Integer
Dim ID(25) As String
Dim Exp(25) As Integer
Dim Pri1(25) As String
Dim Pri2(25) As String
Dim Pri3(25) As String
Dim Item(25) As String
Dim Rank(25) As Integer
Dim IDassign(25) As String
'Sort Names by Experience
'
Range("B3:G7").Select
Selection.Sort Key1:=Range("B4"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
'
'Sort Items by Rank
'
Range("B9:G10").Select
Selection.Sort Key1:=Range("B10"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
'
' Read in Names Count and Items Count
'
Names = Range("Names").Value
Items = Range("Items").Value
Toassign = Range("toassign").Value
'
' Read in Information
'
For i = 1 To Names
ID(i) = Cells(3, i + 1).Value
Exp(i) = Cells(4, i + 1).Value
Pri1(i) = Cells(5, i + 1).Value
Pri2(i) = Cells(6, i + 1).Value
Pri2(i) = Cells(7, i + 1).Value this is incorrect! (Pri3)
Next i
For i = 1 To Toassign
Item(i) = Cells(13, i + 1).Value
Rank(i) = Cells(14, i + 1).Value
Next i
'
'Search items for a match under ID's
'
For i = 1 To Toassign
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj
If Item(i) = Pri1(j) Then GoTo found
nextj:
Next j
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj2
If Item(i) = Pri2(j) Then GoTo found
nextj2:
Next j
For j = 1 To Names
'Check and see if Name(j) still available
If ID(j) = NA Then GoTo nextj3
If Item(i) = Pri3(j) Then GoTo found
nextj3:
Next j
GoTo nexti
found:
IDassign(i) = ID(j)
'Change Name of assigned name to NA
ID(j) = NA
'Change Name of assigned Item to Assigned
Item(i) = Assigned
nexti:
Next i
'If item not found, assign highest ranked item to most
'experienced person available
For i = 1 To Items
' Check to see if item assigned
If Item(i) = Assigned Then GoTo nexti2
For j = 1 To Names
' Check and see if name available
If ID(j) = NA Then GoTo nextj4
'Assign Name to item i
IDassign(i) = ID(j)
'Change Name of assigned name to NA
ID(j) = NA
'Change Name of assigned Item to Assigned
Item(i) = Assigned
GoTo nexti2
nextj4:
Next j
nexti2:
If j = Names Then GoTo allassigned
Next i
allassigned:
' Check to see if any Items not assigned, and if so ID
' them as "Unassigned"
For i = 1 To Toassign
If Item(i) = Assigned Then GoTo nexti3
IDassign(i) = "Unassigned"
nexti3:
Next i
For i = 1 To Toassign
Cells(15, i + 1).Value = IDassign(i)
Next i
End Sub



-----Original Message-----
Hi, I could use some assistance with code that makes
assignments based on a priority in one group with a
ranking in another group.

I have master 2 lists, people and items.
The people contains: Name, experience, priority1,
priority2, priority3.
The items list contains: Name, ranking.

On a separate daily task worksheet assignments need to
be
made to the items that are available with the people
that
are available. The items available and people available
can be different each day.

The items can be in one or more of the three priorities,
and the items ranking may not be unique.

However, as soon the logic is hit, don't consider that
person or item again.

The order for assignments is, check priority1 for all
people available, then priority2, then priority3. If the
item is found under a persons priority, assign the item
to
them on the daily task sheet and do not consider them
for
any other items, or that item for any other person.

If the item is not found in one of the 3 priorities,
assign the highest ranked item (1 is highest) to the
most
experienced person (highest number is the most). Don't
consider that person or item again.

If the item is still not assigned, return unassigned.

e.g.
People:
Name=Name1
Experience=6
priority1= Carrots
priority2=Grapes
priority3=Grapefruit

Name=Name2
Experience=5
priority1= Squash
priority2=Apples
priority3=Bananas

Name=Name3
Experience=6
priority1= Apples
priority2=Squash
priority3= Tomatoes

Items
Name=Apples
Ranking=1

Name=Grapes
Ranking=1

Name=Oranges
Ranking=3

Name=Tomatoes
Ranking=2

Name=Watermelon
Ranking=6

Using the above people and items if apples, tomatoes,
oranges and watermelon are available
Apples are assigned to Name3. (Found first it priority1)
Tomatoes are assigned to Name1 (most experiences,
highest
ranking)
Oranges are assigned to Name2 (next most experienced,
next
highest ranking)
Watermelon is "unassigned"

Thanks, if you are sill here.

.

.

BKing,
In a new sheet copy your list "people" to A1 with headers in row1
copy your list "items" to I1 with headers in row1
so the list "people" will be in range A1:Exx
and the list "items" will be in range I1:Jyy
Copy both subs to a module and run Sub ass()
from the new sheet you just made
HTH
Cecil

Sub ass()

Range("F1").Formula = "Assignment"

LRTB1 = Range("A" & Rows.Count).End(xlUp).row
LRTB2 = Range("I" & Rows.Count).End(xlUp).row

Range("A1:E" & LRTB1).Sort Key1:=Range("B2"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Range("I1:J" & LRTB2).Sort Key1:=Range("J2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

For j = i To 3
Application.Run "priority"
Next j

LRAss = Range("F" & Rows.Count).End(xlUp).row + 1

RemRow = 2 + LRTB1 - LRAss

With Range("I2:J" & RemRow)
..Copy Destination:=Range("F" & LRAss)
..Delete Shift:=xlUp
End With

Range("C1:E1").EntireColumn.Delete

End Sub

Sub priority()

LRTB1 = Range("A" & Rows.Count).End(xlUp).row
LRTB2 = Range("I" & Rows.Count).End(xlUp).row
LRAss = Range("F" & Rows.Count).End(xlUp).row + 1

Range("G" & LRAss).Value = Range("C1")

For i = LRAss To LRTB1
If Not IsError(Evaluate("match(C" & i & "," & _
Range("I1:I" & LRTB2).Address & ",0)")) Then
foundit = Evaluate("match(C" & i & "," & _
Range("I1:I" & LRTB2).Address & ",0)")
Range("F" & i).Value = Range("I" & foundit).Value
Range("I" & foundit, "J" & foundit).Delete Shift:=xlUp
End If
Next i

Range("A" & LRAss & ":F" & LRTB1).Sort Key1:=Range("F2"), _
Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Range("D1:E" & LRTB1).Cut Destination:=Range("C1:D" & LRTB1)

End Sub

"BKing" wrote in message
...
Hi, I could use some assistance with code that makes
assignments based on a priority in one group with a
ranking in another group.

I have master 2 lists, people and items.
The people contains: Name, experience, priority1,
priority2, priority3.
The items list contains: Name, ranking.

On a separate daily task worksheet assignments need to be
made to the items that are available with the people that
are available. The items available and people available
can be different each day.

The items can be in one or more of the three priorities,
and the items ranking may not be unique.

However, as soon the logic is hit, don't consider that
person or item again.

The order for assignments is, check priority1 for all
people available, then priority2, then priority3. If the
item is found under a persons priority, assign the item to
them on the daily task sheet and do not consider them for
any other items, or that item for any other person.

If the item is not found in one of the 3 priorities,
assign the highest ranked item (1 is highest) to the most
experienced person (highest number is the most). Don't
consider that person or item again.

If the item is still not assigned, return unassigned.

e.g.
People:
Name=Name1
Experience=6
priority1= Carrots
priority2=Grapes
priority3=Grapefruit

Name=Name2
Experience=5
priority1= Squash
priority2=Apples
priority3=Bananas

Name=Name3
Experience=6
priority1= Apples
priority2=Squash
priority3= Tomatoes

Items
Name=Apples
Ranking=1

Name=Grapes
Ranking=1

Name=Oranges
Ranking=3

Name=Tomatoes
Ranking=2

Name=Watermelon
Ranking=6

Using the above people and items if apples, tomatoes,
oranges and watermelon are available
Apples are assigned to Name3. (Found first it priority1)
Tomatoes are assigned to Name1 (most experiences, highest
ranking)
Oranges are assigned to Name2 (next most experienced, next
highest ranking)
Watermelon is "unassigned"

Thanks, if you are sill here.