macro to allcate work to users based on specifc rules

Hi Champs,

i have A: E columns with

col A - code (7codes - 001,002,007, 011, 012, 013, 015, 017
Col B - Processor name (empty)
Col C - Work (yet to complete and blanks)
col D - status (F and M)
col E - Days (0 and 1)

i need to fill the empty processor name field (col B) based on the following rules


1. filtering colA - only codes 001, 002 and 007 and col C (only blanks) and col D (status "F") and col E (only "0") ----
after filtering we may get n number of rows... in that 70 count has to be assigned to processor "merYlene" and
another 70 count to processor "kevin" in the col B tab.


2. same way, filtering colA - only codes 001, 002 and 007 and col C (only blanks) and col D (status "M") and col E (only "0")
after filtering, 35 count has to be assigned to processor "Amy" in the col B tab.


3. Finally, Col A (other than 001, 002, 007 ) all the other codes has to be filtered,

ex, filter col A - 011 and with col C (only blanks) and col E (only "0"), in that 70 count has to assigned to processor "david"
filter col A - 012 and with col C (only blanks) and col E (only "0"), in that 70 count has to assigned to processor "rocky"in the col B tab.
filter col A - 013 and with col C (only blanks) and col E (only "0"), in that 70 count has to assigned to processor "kimberly"in the col B tab.
filter col A - 015 and with col C (only blanks) and col E (only "0"), in that 70 count has to assigned to processor "sam"in the col B tab.
filter col A - 017 and with col C (only blanks) and col E (only "0"), in that 70 count has to assigned to processor "merylene"in the col B tab.

pls note that if count for any code is less than 70, all count has to be assigned. if >70, then only 70 count has to be assigned.

Please help me with a macro, Thanks in advance. i have attached sample data and output for the reference. sample allocation.xlsx

Pls any one help me on this

Hi,

I might have a partial solution to your problem (points 1 and 2) but I'm not entirely sure how you'd like the users from point 3 to be assigned. Why the processor "merylene" would come up again if we assigned the records to this person in point 1 - does this refer to the situation when there were less than 70 records meeting criteria from point 1?

Sub assign_users()

Dim i As Integer, merYlene_count As Integer, kevin_count, Amy_count As Integer

merYlene_count = 0
kevin_count = 0
Amy_count = 0

For i = 2 To Range("A1").End(xlDown).Row

    'assigning to merYlene and Kevin
    If (Cells(i, 1) = "001" Or Cells(i, 1) = "002" Or Cells(i, 1) = "007") _
    And Cells(i, 3) = "" And Cells(i, 4) = "F" And Cells(i, 5) = "0" Then
    
        If merYlene_count < 70 Then
        Cells(i, 2) = "merYlene"
        merYlene_count = merYlene_count + 1
        
        ElseIf kevin_count < 70 Then
        Cells(i, 2) = "kevin"
        kevin_count = kevin_count + 1
        
        Else
        
        End If
        
    End If
    
    'assigning to Amy
    If (Cells(i, 1) = "001" Or Cells(i, 1) = "002" Or Cells(i, 1) = "007") _
    And Cells(i, 3) = "" And Cells(i, 4) = "M" And Cells(i, 5) = "0" Then
    
    If Amy_count < 35 Then
    Cells(i, 2) = "Amy"
    Amy_count = Amy_count + 1
    End If
    
    'assigning to other guys
    'CODE
    
    End If

Next i

End Sub

thanks for the code. yes the user "Merylene" has to be assigned both times.

Ok, possibly not the neatest of codes but should do the trick. Let me know if it works as you wanted.

Sub assign_users()

'MACRO for ASSIGNING USERS

Dim i As Integer, merYlene_count As Integer, kevin_count As Integer, Amy_count As Integer, david_count As Integer, rocky_count As Integer, _
kimberly_count As Integer, sam_count As Integer

merYlene_count = 0
kevin_count = 0
Amy_count = 0
david_count = 0
rocky_count = 0
kimberly_count = 0
sam_count = 0

For i = 2 To Range("A1").End(xlDown).Row

    'assigning to merYlene and Kevin
    If (Cells(i, 1) = "001" Or Cells(i, 1) = "002" Or Cells(i, 1) = "007") _
    And Cells(i, 3) = "" And Cells(i, 4) = "F" And Cells(i, 5) = "0" Then
    
        If merYlene_count < 70 Then
        Cells(i, 2) = "merYlene"
        merYlene_count = merYlene_count + 1
        
        ElseIf kevin_count < 70 Then
        Cells(i, 2) = "kevin"
        kevin_count = kevin_count + 1
        
        Else
        
        End If
        
    End If
    
    'assigning to Amy
    If (Cells(i, 1) = "001" Or Cells(i, 1) = "002" Or Cells(i, 1) = "007") _
    And Cells(i, 3) = "" And Cells(i, 4) = "M" And Cells(i, 5) = "0" Then
    
        If Amy_count < 35 Then
        Cells(i, 2) = "Amy"
        Amy_count = Amy_count + 1
        End If
    
    End If

    'assigning to david
    If Cells(i, 1) = "011" And Cells(i, 3) = "" And Cells(i, 5) = "0" Then
    
        If david_count < 70 Then
        Cells(i, 2) = "david"
        david_count = david_count + 1
        End If
    
    End If
    
    'assigning to rocky
    If Cells(i, 1) = "012" And Cells(i, 3) = "" And Cells(i, 5) = "0" Then
    
        If rocky_count < 70 Then
        Cells(i, 2) = "rocky"
        rocky_count = rocky_count + 1
        End If
    
    End If

    'assigning to kimberly
    If Cells(i, 1) = "013" And Cells(i, 3) = "" And Cells(i, 5) = "0" Then
    
        If kimberly_count < 70 Then
        Cells(i, 2) = "kimberly"
        kimberly_count = kimberly_count + 1
        End If
    
    End If
    
    'assigning to sam
    If Cells(i, 1) = "015" And Cells(i, 3) = "" And Cells(i, 5) = "0" Then
    
        If sam_count < 70 Then
        Cells(i, 2) = "sam"
        sam_count = sam_count + 1
        End If
    
    End If

Next i

'merYlene if still less than 70 assigned to this user
   
    For i = 2 To Range("A1").End(xlDown).Row
    
        If Cells(i, 1) = "017" And Cells(i, 2) = "" And Cells(i, 3) = "" And Cells(i, 5) = "0" Then
    
            If merYlene_count < 70 Then
            Cells(i, 2) = "merYlene"
            merYlene_count = merYlene_count + 1
            Else
            Exit For
            End If
        
        End If
    
    Next i
    
End Sub