+ Reply to Thread
Results 1 to 6 of 6

How to group range data based on conditions

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    13

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    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 ...
    Attached Images Attached Images
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    13

    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
    Jayapresad

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    13

    Re: How to group range data based on conditions

    Dear JeteMC,

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

    With love

    Jayapresad

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sum a group of cells based on data range
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-04-2019, 09:58 PM
  2. Pivot and/or chart to group data by conditions
    By DT211569x in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-29-2018, 04:28 PM
  3. [SOLVED] Formula to transfer data from one range of cells to another based on conditions
    By RachelMads02 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-30-2017, 02:56 PM
  4. Calculate Stdev for a group of cells based on multiple conditions
    By dsgeller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2014, 09:58 AM
  5. VBA code to copy a range of data based on conditions
    By rshnkmr39 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2014, 09:00 AM
  6. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  7. Macro to Group Rows Along a Range Based on Values in a Column
    By JHCali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-06-2013, 10:20 AM

Bookmarks

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