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

**kamikan** · 05-29-2020, 12:48 PM

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...)

**JeteMc** · 05-31-2020, 12:53 AM

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:

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.