Changing values in an Array

Question

My problem is basically as follows:

That is, I want to change some values in an array according to a list of "transactions".

In the picture, you can see only 2 transactions but there can be many more.

I have achieved a solution for half of my array:

But I cannot get to the desired final array, after all the transactions have been processed. I am a bit lost.

Any hints are highly appreciated

Answer 1

You may try MAKEARRAY() with FILTER() function.

=MAKEARRAY(ROWS(C5:F8),COLUMNS(C5:F8),LAMBDA(r,c,FILTER(J11:J12,(H11:H12=r)*(I11:I12=c),INDEX(C5:F8,r,c))))

Answer 2

This is quite possible without using any LAMBDA() helper function, here is one alternative:

---

• Method One: Formula used in cell C14

=LET(
     a, C5:F8,
     b, TOCOL(a),
     c, ROWS(b),
     d, COLUMNS(a),
     e, MOD((SEQUENCE(c)-1),d)+1,
     f, INT((SEQUENCE(c)-1)/d)+1,
     g, MMULT((f=TOROW(H11:H12))*(e=TOROW(I11:I12))*TOROW(J11:J12),{1;1}),
     h, WRAPROWS(IF(g=0,b,g),d),
     h)

---

For the above method if for any instance there is a 0 as new value, then it will return false positives, as already mentioned by rotabor Sir, therefore can use the amended version as well.

=LET(
     a, C5:F8,
     b, TOCOL(a),
     c, ROWS(b),
     d, COLUMNS(a),
     e, MOD((SEQUENCE(c)-1),d)+1,
     f, INT((SEQUENCE(c)-1)/d)+1,
     WRAPROWS(IFNA(XLOOKUP(f&"|"&e,
                           H11:H12&"|"&I11:I12,
                           J11:J12),b),d))

---

Toggle around the variables to find what each of those returns.

Change this {1;1} to, if its multiple, with some other alternatives -->

SEQUENCE(ROWS(J11:J12))^0

---

Or,

SEQUENCE(ROWS(J11:J12),,1,0)

---

• Method Two: Another crude way i believe is using XLOOKUP() and IFNA()

---

=IFNA(XLOOKUP(B5:B8&"|"&C4:F4,H11:H12&"|"&I11:I12,J11:J12),C5:F8)

---

To use with LET() for the above:

=LET(
     α, C5:F8,
     δ, SEQUENCE(ROWS(α)),
     ε, SEQUENCE(,COLUMNS(α)),
     IFNA(XLOOKUP(δ&"|"&ε, 
                  H11:H12&"|"&I11:I12, 
                  J11:J12), α))

---

• Method Three : Using REDUCE()

=REDUCE(C5:F8,J11:J12,LAMBDA(a,v,
 MAKEARRAY(B8,B8,LAMBDA(r,c,
 LET(L,TAKE(+H12:v,1),IF((r=@L)*(c=(@DROP(L,,1))),v,
 INDEX(a,r,c)))))))

---

• Method Four: Using MAP()

=LET(
     a, IF(SEQUENCE(,4),B5:B8),
     b, IF(SEQUENCE(4), C4:F4),
     IFNA(MAP(a,b,LAMBDA(x,y,
     XLOOKUP(1,(H11:H12=x)*(I11:I12=y),J11:J12))),C5:F8))

---