Grouping Items in Excel...!

[uday peddineni]

Hi All,

I have a Requirement to group Items based on Volume and all Groups should have approximately equal Volume of Items.

 Example:-

We have Item name and No of Items and we want to divide Items in to Groups.

Based on the sum of all the groups

Group1 :- 100000 items Total Group2 :- 10000 Total Group 3:- 9000 Total Group 4:- 6580 Total Group 5:- 7000 

In this example , Group 1 has only one item(Item 8) since it has high volume.Same case for group 2 and group 3.We distributed remaining items to group 4 and group 5.

Note : As per our requirement , we can split items to multiple groups. One item can belong to only 1 group. A group can have multiple items.We understand that equal distribution may  not be possible but we are looking for formula which distributes the items best possible.

			Expected Output
Item Name	No of Items		Group1	Group2	Group3	Group4	Group5
Item 1	10		100000	10000	9000	10	2000
Item 2	20					20	1000
Item 3	500					500	3000
Item 4	1000					200
Item 5	200					300
Item 6	300					5000
Item 7	10000					100
Item 8	100000					400
Item 9	5000					50
Item 10	3000
Item 11	100
Item 12	400
Item 13	9000
Item 14	50
Item 15	2000