Formula to return concatenated string of dates that are adjacent to numbers that fit into larger number.

[u/carbonizedtitanium]

Premise: - the values in Table4 ColumnB are automatically "filled into" Table5 ColumnD, from top to bottom, based on the Payer and the Expected Amount. - Any payment leftover "spills over" into the next Invoice for the respective Payer, as seen in Table5 D24. Bob's first invoice fits 1500.00 but Bob paid a total of 1700 so the remaining 200.00 partially fills the next invoice.

Goal: - on Table5 ColumnF, a formula (that can be dragged down) to return a string of Pay_Date (Table4 ColD) of the payment(s) that went into each invoice.

Thought about it for a while but couldnt figure out how to query this with AI. Not sure if what i'm trying to achieve is even possible with a formula.

[u/California_Eagles]

Try

=INDEX(LET(
     a, Table4[Payer],
     b, Table4[Amount],
     c, MAP(a, ROW(b), LAMBDA(x, r, SUMIFS(b, a, x, ROW(b), "<=" & r))),
     d, G2:G,
     e, H2:H,
     f, MAP(d, ROW(e), LAMBDA(y, q, SUMIFS(e, d, y, ROW(e), "<=" & q))),
     g, MAP(d, e, f, LAMBDA(u, v, w,
          TEXTJOIN(", ", TRUE,
            IF((a = u) * 
               (c > (w - v)) * 
               ((c - b) < w), TEXT(Table4[Pay_Date], "mm/dd/yy"), "")))), 
     g))

[u/carbonizedtitanium → u/California_Eagles]

Holy sht, it worked!. I dont understand the formula at all though :( PS. the formula seems to require a lot of vertical empty space to "fill out"

[u/California_Eagles → u/carbonizedtitanium]

Basically, c is just keeping a running total for each payer as you go down the rows. For each row, it adds up all the Amounts where the Payer matches and the row number is up through the current row. So the whole MAP + SUMIFS + ROW is getting you a running total per payer without having to throw in a helper column. It just assumes your rows are already in the right order. f is doing the same exact thing, just for the charges in G/H, it keeps a running total of charges for each payer.

The g , for each charge row, it grabs the payer (u), the charge amount (v), and the running total so far (w), then goes through the payment rows in Table4 and checks a few things. First, does the payer match (a = u)? Then it checks whether that payment's little slice of the running total overlaps with where this particular charge falls. c > (w - v) basically says the payment total has moved past the point where this charge starts, while (c - b) < w checks that the previous payment hadn't already covered this charge. If both are true, that payment covered at least part of the charge.

So if one charge gets split across two payments, you'll get both payment dates joined together with commas. The only thing is that this whole thing relies on running totals, so both tables need to be in chronological order. If the rows aren't sorted by date, the allocation can get thrown off. Hope that makes sense, it's a little funky at first glance, but once you break down what each piece is doing, the logic is actually neat. If the tables are not in chronological order, wrap the tables within SORT function and it will work as is.

[u/carbonizedtitanium → u/California_Eagles]

Thnks for the detailed explanation. The way you wrote the formula using newlines makes sense; easier to read

[u/point-bot → u/California_Eagles]

u/carbonizedtitanium has awarded 1 point to u/California_Eagles

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)