# How to group range data based on conditions

1. ## How to group range data based on conditions

Problem
How to pack these items in the range with minimum number of carton subject to
1. Total weight in a carton should between 20 kgs to 30 Kgs.
2. In a caton max 5 items can be inluded

2. ## Re: How to group range data based on conditions

You have published the question in Access sub-forum, but how about using Excel for that task?

If there is not more than some 20 packs (some 100 items) Solver (an add in to Excel, which is installed by default, but not activated during standard installation) could handle it.
See attached file.
I first estimated max number of needed packs as =ROUNDUP(SUM(D7:D18)/20,0)
then prepared a table with numbers form 1 to this max in column E, then count of each pack items (using COUNTIF) and Total weight of each pack (using SUMIF) and finally the condition whether pack weight is exactly 0 (no items - so will be ignored) or >=20 =OR(J2=0,J2>=20) in K2 and copied down
the aim will be to maximize the number of fulfilled conditions (don't worry about others like max 5 items per pack, or max weight of pack <=30. These will be added in Solver).

See solver setup on attached screenshot and in the file. As the start point I added all items to one pack :-) so wrote 1 in all E7:E18 cells. then started solver. Note that you may (and probably will obtain different result as there will be more than one possible solution. Also note that there is no constrain to use only small pack numbers. so in the solution pack no1 is ommited. if you got such solution you could change (Ctrl+h) 4 to 1, or you could re-run the solver but with one row less in columns H:K (so allowing only packs no: 1..3.

Final note - this approach could be used probably for up to some 50 items. Then the solution search time will be rather long. And for even more items (probably some 150 may be 200 or more) Solver will communicate: "too many variables".
To help solver a bit you may sort your input table by weight (descending) and then assign pack numbers in sequence 1 2 3 4 4 3 2 1 1 2 3 4 4 3 2 1 … of course 4 here is an estimated pack count if you have 6 then the sequence will be 1 2 3 4 5 6 6 5 4 3 2 1 1 2 3 ...

3. ## Re: How to group range data based on conditions

Dear Kaper,

Thanks for your inputs and let me use the same to solve my problem.
With love

4. ## Re: How to group range data based on conditions

I am going to post the following as an Excel formula based option.
Cell H3 contains the weight limit and cell H4 contains the number of items limit.
A table is set up using the formula: =IF(OR(COUNTIFS(G\$6:G6,">0")>=\$H\$4,SUM(MAX(G\$6:G6),\$D7)>\$H\$3,SUM(\$F7:F7)>0),0,SUM(MAX(G\$6:G6),\$D7))
The column is populated using: =INDEX(G\$5:J\$5,AGGREGATE(15,6,(COLUMN(G7:J7)-COLUMN(F7))/(G7:J7>0),1))
Let us know if you have any questions.

5. ## Re: How to group range data based on conditions

Dear JeteMC,

Thanks for your solution and let me try for the same.

With love

6. ## Re: How to group range data based on conditions

You're Welcome and thank you for the feedback. If we have answered all of your questions, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1