Help!!! Help Help!!!

live2aid

New Member
Joined
Nov 22, 2009
Messages
14

Hi,
I have to balance workload equally for everyone(Name:abc,mno,xyz) and New orders must be shared or distributed equally for every person.

New Orders must be shared in such a way that every person's percentage share must be made equal by distributing or sharing New Orders.

Here's example to show how it work manually.

Example 1:Orders balanced initially. (Only for uderstanding purpose).


New Orders: 6​


<table style="width: 1019pt; border-collapse: collapse;" width="1357" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 31pt;" width="41"><col style="width: 143pt;" width="191"><col style="width: 136pt;" width="181"><col style="width: 56pt;" width="74"><col style="width: 179pt;" width="238"><col style="width: 156pt;" width="208"><col style="width: 170pt;" width="227"><col style="width: 148pt;" width="197"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-color: rgb(236, 233, 216); width: 31pt; height: 12.75pt; background-color: transparent;" width="41" height="17">Name</td><td class="xl65" style="border-color: rgb(236, 233, 216); width: 143pt; background-color: transparent;" width="191">Total before updating new order</td><td class="xl65" style="border-color: rgb(236, 233, 216); width: 136pt; background-color: transparent;" width="181">Total after updating new order</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; width: 56pt; background-color: transparent;" width="74">
</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; width: 179pt; background-color: transparent;" width="238">Data</td><td class="xl67" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; width: 156pt; background-color: transparent;" width="208">
</td><td class="xl67" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; width: 170pt; background-color: transparent;" width="227">
</td><td class="xl68" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; width: 148pt; background-color: transparent;" width="197">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">abc</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">1</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">Name</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">Sum of Total before updating new order</td><td class="xl72" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;">% Total before updating new order</td><td class="xl72" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;">Sum of Total after updating new order</td><td class="xl71" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; background-color: transparent;">% Total after updating new order</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">mno</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">1</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">abc</td><td class="xl66" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;" align="right">1</td><td class="xl74" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td><td class="xl72" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;" align="right">3</td><td class="xl77" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">xyz</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">1</td><td class="xl65" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td class="xl69" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;">mno</td><td class="xl69" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;" align="right">1</td><td class="xl75" style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">33.33%</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">3</td><td class="xl78" style="border-color: rgb(236, 233, 216) black rgb(236, 233, 216) rgb(236, 233, 216); border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl69" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;">xyz</td><td class="xl69" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;" align="right">1</td><td class="xl75" style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">33.33%</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">3</td><td class="xl78" style="border-color: rgb(236, 233, 216) black rgb(236, 233, 216) rgb(236, 233, 216); border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl70" style="border-color: black rgb(236, 233, 216) black black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;">Grand Total</td><td class="xl70" style="border-color: black rgb(236, 233, 216) black black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">3</td><td class="xl76" style="border-color: black rgb(236, 233, 216); border-top: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">100.00%</td><td class="xl73" style="border-color: black rgb(236, 233, 216); border-top: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">9</td><td class="xl79" style="border-color: black black black rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">100.00%</td></tr></tbody></table>​





Here in example1, every person has orders 1 initially and is balanced.


Now new orders available is 6 and must be equally distributed to all.​


So total new orders (6) divided by total number of persons(3) equal to two (2).​


i.e 6/3 = 2​


i.e quotient is added with column named "Total before updating new order" to get results in column named "Total after updating new order" so that their percentage share is equal i.e 33.33%.






Example 2: Orders unbalanced initially. (Real issue or problem)





New Orders:4


<table style="width: 701pt; border-collapse: collapse;" width="933" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 31pt;" width="41"><col style="width: 55pt;" width="73"><col style="width: 95pt;" width="127"><col style="width: 48pt;" width="64"><col style="width: 56pt;" width="74"><col style="width: 89pt;" width="119"><col style="width: 78pt;" width="104"><col style="width: 130pt;" width="173"><col style="width: 119pt;" width="158"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl72" style="border-color: rgb(236, 233, 216); width: 31pt; height: 12.75pt; background-color: transparent;" width="41" height="17">Name</td><td class="xl72" style="border-color: rgb(236, 233, 216); width: 55pt; background-color: transparent;" width="73">Total(initial)</td><td class="xl72" style="border-color: rgb(236, 233, 216); width: 95pt; background-color: transparent;" width="127">Total after Balancing</td><td style="border-color: rgb(236, 233, 216); width: 48pt; background-color: transparent;" width="64">
</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; width: 56pt; background-color: transparent;" width="74">
</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; width: 89pt; background-color: transparent;" width="119">Data</td><td class="xl64" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; width: 78pt; background-color: transparent;" width="104">
</td><td class="xl64" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; width: 130pt; background-color: transparent;" width="173">
</td><td class="xl65" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; width: 119pt; background-color: transparent;" width="158">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl72" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">abc</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">0</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">Name</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">Sum of Total(initial)</td><td class="xl73" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;">% of Total(initial)</td><td class="xl73" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;">Sum of Total after Balancing</td><td class="xl68" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; background-color: transparent;">% of Total after Balancing</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl72" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">mno</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;">abc</td><td class="xl63" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216) black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; background-color: transparent;" align="right">0</td><td class="xl75" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;" align="right">0.00%</td><td class="xl73" style="border-color: black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; background-color: transparent;" align="right">3</td><td class="xl69" style="border-color: black black rgb(236, 233, 216) rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl72" style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">xyz</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">2</td><td class="xl72" style="border-color: rgb(236, 233, 216); background-color: transparent;">3</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl66" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;">mno</td><td class="xl66" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;" align="right">3</td><td class="xl76" style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">60.00%</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">3</td><td class="xl70" style="border-color: rgb(236, 233, 216) black rgb(236, 233, 216) rgb(236, 233, 216); border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl66" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;">xyz</td><td class="xl66" style="border-color: rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216) black; border-left: 0.5pt solid black; background-color: transparent;" align="right">2</td><td class="xl76" style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">40.00%</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;" align="right">3</td><td class="xl70" style="border-color: rgb(236, 233, 216) black rgb(236, 233, 216) rgb(236, 233, 216); border-right: 0.5pt solid black; background-color: transparent;" align="right">33.33%</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-color: rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" height="17">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td style="border-color: rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl67" style="border-color: black rgb(236, 233, 216) black black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;">Grand Total</td><td class="xl67" style="border-color: black rgb(236, 233, 216) black black; border-top: 0.5pt solid black; border-left: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">5</td><td class="xl77" style="border-color: black rgb(236, 233, 216); border-top: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">100.00%</td><td class="xl74" style="border-color: black rgb(236, 233, 216); border-top: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">9</td><td class="xl71" style="border-color: black black black rgb(236, 233, 216); border-top: 0.5pt solid black; border-right: 0.5pt solid black; border-bottom: 0.5pt solid black; background-color: transparent;" align="right">100.00%</td></tr></tbody></table>​





Manual procedure:
  • Person having maximum share (Name: 'mno' with 3 orders & percentage share is 60 %) must be neglected or not considered.
  • Person having minimum share (Name: 'abc' with 0 orders & percentage share is 0%) must be considered.
  • I have simple logic to do this using for loop & if loop condition or statements, but not sure how to use it or to program. Here is the logic:
  • If Person with minimum orders i.e abc having 0 orders Then he must get first order out of available New Orders(Here New Orders is 4).
  • During first iteration: Person 'abc' order must INCREMENT from 0 to 1 (acsending) and similarly New Orders must DECREMENT from 4 to 3(descending). since one out of 4 new orders is given to person with minimum share.
  • During second iteration: Person 'abc' still has minimum share(i.e 1) and again he will get another order from New orders. Now person 'abc' has 2 orders(i.e incremented from 1 to 2) and remaining New Orders will be 2(i.e decremented from 3 to 2).
  • During third iteration: Person 'abc' and 'xyz' has equal share of orders 2 respectively. Now New orders must be shared or distributed according to rowwise. So that 'abc' and 'xyz' will have 3 and 3 as their orders respectively and hence workload is balanced.
<!-- / message -->

Hope you people help me. I will be looking forward to a favourable reply. thanks in Advance.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,874
Members
413,945
Latest member
V51773

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top