+ Reply to Thread
Results 1 to 2 of 2

Sort data table into sets/groups where sum of X<40, Y<80 and z<50

  1. #1
    Registered User
    Join Date
    05-29-2020
    Location
    uk
    MS-Off Ver
    Microsoft Excel Latest version 2019 16.0.
    Posts
    1

    Sort data table into sets/groups where sum of X<40, Y<80 and z<50

    Hi there, first time user post, however I have always admired from affar.

    I have come under a bit of stress at work and need to plan a months worth of work by the end of next week. I have a list of data which looks something like:
    Visit A B C
    Visit 1 5 10 3
    Visit 2 9 17 4
    Visit 3 2 6 4
    Visit 4 6 10 11
    Visit 5 5 12 7
    Visit 6 7 6 2

    I have only shown 6 visits, but I have to plan over 300 visits that need to be performed during July. I need to work out what days we can perform each of the 300 visits, where the total of constraint A, B and C cannot be more than, A <40, B <80 and C< 50. Is anyone able to help me produce a formula via VBA or solver that would perform a first fit decreasing algorithm, and sort the list into groups (effectively days) i.e day one perform visits 1,3,4... day two perform (2,5,7...)

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,100

    Re: Sort data table into sets/groups where sum of X<40, Y<80 and z<50

    Hello kamikan and Welcome to Excel Forum.
    Perhaps this will help relieve some of the stress.
    In the attached file 100 visits are listed in column A with corresponding values in column B:D
    Row 1 is populated with the weekdays in July.
    Column E is populated using the formula: =AND(SUM($B$2:$B2)<40,SUM($C$2:$C2)<80,SUM($D$2:$D2)<50) which would manually need to be dragged down the column at least until it displays a blank cell.
    Starting with column F the rest of the sheet is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The font color is set to white.
    Conditional formatting is used to display a green fill for those cells that have a value of TRUE.
    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.

+ 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. Replies: 26
    Last Post: 01-15-2016, 03:38 AM
  2. Sort Data into Groups
    By Agro12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2015, 06:58 AM
  3. How to display averages, min and max from varying data sets and sub groups
    By shoey_123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2013, 09:55 PM
  4. Can't figure out how to sort groups of data
    By Sathed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2012, 11:51 AM
  5. Sort two different groups of data
    By darkjedi in forum Excel General
    Replies: 2
    Last Post: 02-09-2012, 05:12 PM
  6. Need to sort groups of data by name or date
    By mockturtle29 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-08-2011, 07:29 PM
  7. Replies: 1
    Last Post: 07-22-2010, 08:39 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