• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel HELP! How to divide files among employees based on page count

A

angelokur

New Member
Hello, I've attached an excel file with the problem I'm hoping to solve. Column A lists the file names, and Column B lists the page numbers in each file. The page numbers are totaled in Column C. My job is to distribute the files to the five employees based on the number of pages. I also have to keep in mind when dividing the files that employees A, B & C should each get 25% of the files and employees D & E should only get 12.5% each. Column D contains the formula I'm currently using, and it mostly works. However, you will notice that Employee B is only assigned 20% of the files, while Employee C is assigned 30% of the files. It is driven by the file on row 77 (highlighted in yellow). This 901-page file exceeds employee B's page limit and is assigned to employee C. I can reassign this to employee B manually, but I was hoping for some assistance in updating my formula to make this work without having to update manually each time.
Thank you very much!
 

Attachments

  • Assigning Files to Employees.xlsx
    25.6 KB · Views: 7
angelokur
Here one other kind of sample until You'll get formula-solution.
Press that button and You'll see ... it'll assign.
 

Attachments

  • Assigning Files to Employees.xlsb
    28.3 KB · Views: 8
vletm said:
angelokur
Here one other kind of sample until You'll get formula-solution.
Press that button and You'll see ... it'll assign.
Click to expand...
Wow! This is amazing. Thank you very much.
I am familiar with what macros are but do not know how to actually write one myself. Would you be able to walkthrough your logic? I don't mean the actual VBC code but your thought process or the math involved? That will help me understand how to macro assigns the files to the 5 employees and even come up with an excel formula to do it myself.
Again, I really appreciate your help!
 
angelokur
This version works with 'any number of files'.
Basic logic after cleared old values away... by 'my way'
#1 Get next employee
#2 Solve needed pages for employee ( do_g )
#3 Assign maximum pages which have not assigned and which is less than #2-value ( do_g )
#4 Record employees do_g after #3
#5 Loop steps #1 ... #4 until there will be less than 3 to do ( do_g ) to have soft end of assign
#6 Do basic same as above with each employees recorded do_g-values per maximum do_g
This way all employees could get 'big pages' too.
With this, You can see - how it works - instead of only results.
Note: This is a sample.
There could be combinations which makes endless loop
I tried to assign only with Your given data.
 

Attachments

  • Assigning Files to Employees.xlsb
    29.5 KB · Views: 5
Thank you very much! You are awesome.
Do you think its possible to use "regular" excel functions and formulas to achieve this without VBA?
 
angelokur
Many things are possible,
but ... what are "regular" excel functions and so on?
Me, I would think to do this only with VBA.
Why You won't use VBA? Is there many good reasons or what?
 
I was thinking along the lines of IF statements, Index/Match or OFFSET etc. common functions that are used in excel.
I have no doubt that VBA is probably the best way to go. However, I don't come from any programming background and my understanding of VBA is poor. I am hoping to learn it but I was wondering if it could be done without VBA.
Anyway, I really appreciate you taking the time to help me out with this. Thank you!
 
You must log in or register to reply here.
Back
Top