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?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.