Automate Spreadsheet Calculation

Question

I'm trying to build out a spreadsheet formula that will allow me to take one list of numbers and evenly distribute them into another list of numbers. Attaching an example below.

I'm sure there's a way to automate this process but I've done extensive research online and can't seem to figure out the right combination of existing formulas to make this work, would greatly appreciate any resources or tips to point me in the right direction. Currently using G Sheets.

Example spreadsheet: https://docs.google.com/spreadsheets/d/1JgFKXGJ2-eQEXAGtqu64p_Zw7gY2fVpEtuCFDvMb9YE/edit#gid=1179066278

List 1:
1000
500

List #2:
300
600
200
100
200
100

Desired result:

List 1 value -> List 2 values that add up to List 1 value

1000 -> 300, 600, 100
500 -> 200, 100, 200

user_5632629 · Answer

try this partial solution:

=ARRAYFORMULA(TRANSPOSE(QUERY({        FILTER(A:A, A:A<=C3, A:A<>""), MMULT(
 TRANSPOSE((ROW(INDIRECT("B1:B"&COUNTA(FILTER(A:A, A:A<=C3, A:A<>""))))<=
 TRANSPOSE( ROW(INDIRECT("B1:B"&COUNTA(FILTER(A:A, A:A<=C3, A:A<>""))))))*
 FILTER(A:A, A:A<=C3, A:A<>"")),  SIGN(FILTER(A:A, A:A<=C3, A:A<>"")))}, 
 "select Col1 where Col2 <="&C3)))

side note: it will give you only the exact number (as you request) or lower number (one number short)

Automate Spreadsheet Calculation

1 Answers