Taking as many items from an ordered list as necessary to sum to a value

[BenBl]

Hey all,

I have a list of orders sorted by priority. Each order has a list of associated tasks sorted by step sequence. Each task has a duration.

e.g.

Order 1 priority 3:
Step 1) Unclamp 25 pieces. - 1 hour
Step 2) Mask 25 pieces. - 3 hours
step 3) Paint 25 pieces - 2 hours

Order 2 priority 2:
Step 1) Unclamp 13pieces. - .5 hour
Step 2) Mask 13 pieces. - 1.5 hours
step 3) Paint 13 pieces - 1 hours

Order 3 priority 1:
Step 1) Heat 60 pieces - 1.5 hours
Step 2) Package 60 pieces - 5 hours.

I want to pick all the tasks that sum to less than an amount of person-hours. For example, say I want to schedule 11 person hours for tomorrow.

Start at the highest priority, and add as many steps as possible to a total of less than 11 hours.

O3 S1 (1.5 hours), 03 S2 (5 hours), O2 S1 (0.5 hours), O2 S2 (1.5 hours), O2 S3 (1 hour), O1 S1 (1 hour) STOP. (We can’t add O1 S2 without going above 11 hours).

How do you recommend doing this? Do I need to use a Steve-recursive actions hack or is there a formula that will do the trick?

[Marc_Dillon]

Oooo, that’s an interesting challenge! I’ll give it a quick shot here.

Hmmm, let’s assume you are needing to assign a bunch of these to multiple people, yah? And possibly every day? So I think we’ll need 2 columns in the tasks table, [person] and [date]. When both of these are filled out (at the same time), that’ll indicate that a task has been “covered” or “assigned”.

Let’s next make a slice for “my tasks today”

AND( [person] = USEREMAIL() , [date] = TODAY() )

Let’s also make a slice for “my available tasks to assign”

AND( 
  ISBLANK( [person] ) , 
  ISBLANK( [date] ) ,
  [task time] <= 11 - SUM( my tasks today[task time] )
 )

Then we can select the next task to assign with:

INDEX( ORDERBY( 
  my available tasks to assign[key-column] ,
  [priority]
  ) , 1 
)

Then, yes, we’ll use “recursive” actions. Setup an Action that sets the [person] and [date] column. Then I think you just have to loop over that single action, with a looping condition of ISNOTBLANK( my available tasks to assign[key-column] ).

Good luck!