Group Items based on Criteria

[crazydeo]

Hi, not sure how to best use excel to solve the following....

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Gender[/TD]
[TD]Material[/TD]
[TD]Qty[/TD]
[/TR]
</tbody>[/TABLE]

I have the following information. I need to group the Materials by gender with a max of 5000 qty per group without partially splitting the materials into 2 separate groups.

Any help or direction would be greatly appreciated!!

 

[oldbrewer]

is steel "male" and coal "female"

seriously give us a few rows of raw data with a table of your expected results

 

[crazydeo]

Here is the sample data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Gender[/TD]
[TD]Material[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-11[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-12[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-13[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-14[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-15[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-16[/TD]
[TD]245[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-17[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-18[/TD]
[TD]4900[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-19[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-20[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


Want it to be organized by gender, then material, grouped in qty of 5000 units max

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Gender[/TD]
[TD]Material[/TD]
[TD]QTY[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-19[/TD]
[TD]2500[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-12[/TD]
[TD]1500[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-13[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-18[/TD]
[TD]4900[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]MAT-20[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-14[/TD]
[TD]4500[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-11[/TD]
[TD]250[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-16[/TD]
[TD]245[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-17[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]MAT-15[/TD]
[TD]2500[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Hope this clarifies it!

 

[oldbrewer]

why does lower table start with women mat 19 2500
why are all women before any men

I do see how a group must not contain more than 5000 items

 

[crazydeo]

why does lower table start with women mat 19 2500
why are all women before any men

I do see how a group must not contain more than 5000 items

Basically we need to clear our warehouse of women's materials first then men's. So women's is first to be loaded on the truck. Truck only can hold about 5000 units. So once we clear all the women's then we can focus on men. As far as material goes it could be in any order as long as we group women first then men and so forth. Also, if we are completely done with women then we can fill the truck with men's materials to equal 5000 units.