Grouping Items in Excel...!

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

excel

Forum Discussion

Grouping Items in Excel...!

Resources