2

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
gbmytabc
  • 21
  • 4

1 Answers1

2

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)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Hey! Thanks for your response. This is helpful to get me started but I'm a little lost on what's happening in your partial solution. I understand what these funcs do on their own but I'm not sure how this works when they come together. Sorry that I'm asking a pretty broad question here, but can you explain a little about what this is doing in laymen's terms? I'm assuming TRANSPOSE has something to do with how MMULT arrays have to be structured and I see that you're using ROW(INDIRECT()) to get the range of values that are <= selected cell in Column B, but beyond that you lose me. – gbmytabc Dec 27 '20 at 23:30
  • @gbmytabc simply put after I filter out values which are less than value in column C (for C2 those are all values from A column without A2) I run on them a running total with MMULT. then I pot that running total in array {} with filtered values and run a simple query to get values of filtered A column based on the running total helper column. this formula would be pretty easy with restricted range like A1:A6 but it gets complicated with those INDIRECT formulas in addition with FILTERed array. maybe this could help to visualize better whats going on: https://stackoverflow.com/a/57026166/5632629 – player0 Dec 28 '20 at 00:18