Tough one...

[Matt]

Not sure if this one is do-able but say I have the following
information:

Job Priority Status Ready?

Beta 5
Gamma 9 Closed
Sigma 1 Yes
Alpha 10
Epsilon 4 Yes
Kappa 9 Yes
Theta 5 Closed Yes

I want to prioritize the items in the jobs column. There may be more
jobs added to the list so I would like the function to look through up
to 100 rows instead of just the ones currently listed in rows 1-6.
Each job has a priority assigned to it (from 1-lowest to 10-highest).
Some jobs may have equal priority, in which case the one that appears
first in the list would be first then the one next in the list and so
on. The tricky part is that there are two other columns associated
with each job (status and ready). Jobs that are already closed should
not appear in the priority list. Also, items that are not ready
should not appear in the priority list. Ideally then, the function
would give the top x priority items, so for the example above the top
5 items would give:

Kappa
Epsilon
Sigma
#NA
#NA

 

[Don Guillett]

try this idea where your 1st data column is F.

Sub priortizejobs()
lr = Cells(Rows.Count, "G").End(xlUp).Row
r = 2
For i = 10 To 1 Step -1
For ii = 2 To lr
If Cells(ii, "G") = i And _
Cells(ii, "h") <> "Closed" And _
Cells(ii, "i") = "Yes" Then
Cells(r, "j") = Cells(ii, "f")
r = r + 1
End If
Next ii
Next i
End Sub

 

[Guest]

Have you tried sorting using Status as the first criteria, Ready as the
second and Priority as the third?