Trying to sum arrays that overlap by grabbing data from a table-CodePudding

my issue is hard to explain so I'm going to give you an example. Let the sample arrays be:

array1: 1,2,0,4

array2: 0,0,1,0

array3: 0,1,6,2

I have these numbers separated by cell and not comma, but that doesn't matter.

Next step is: I type (as text, not formula) array1 array2 in a column, and I want to get the result. So first example: 1| array1 array2

would give:

1| 1 (array1 1st value) 0 (array2 1st value) = 1

2| 2 (array1 2nd value) 0 (array2 2nd value) = 2

3| 0 (arra1 3rd value) 1 (array2 3rd value) = 1

4| 4 (array1 4th value) 0 (array2 4th value) = 4

Another example (different cells):

1| array1

2| array2

this would return=

1| 1 (array1 1st value) = 1 (there is no other array value there so nothing else is added)

2| 2 (array1 2nd value) 0 (array2 1st value) = 2

3| 0 (arra1 3rd value) 0 (array2 2rd value) = 0 4|

4 (array1 4th value) 1 (array2 3rd value) = 5

5| 0 (array2 4th value) = 0

Third example:

1| array1 array3

2|

3| array2

this returns=

1| 1 (arr1 1st) 0 (arr3 1st) = 1

2| 2 (arr1 2nd) 1 (arr3 2nd) = 3

3| 0 (arr1 3rd) 6 (arr3 3rd) 0 (arr2 1st) = 6

4| 4 (arr1 4th) 2 (arr3 4th) 0 (arr2 2nd) = 6

5| 1 (arr2 3rd) = 1 6| 0 (arr2 (4th) = 0

Notes: the arrays aren't limited to 4 values, it depends on my database and they go up to 26 cells (1 per value for each array name). I reduced them to 4 for this example only. My database is static though, so you don't need to worry about array2 becoming something else mid calculations. If array2 is "a b c d" it will be that until the end.

Some ways to interpret this

So in other words I want to treat them as arrays that would have overlapped otherwise, but keep adding the values. Another way to see it is imagine REF errors didn't exist due to overlapping a cell, and it just kept going. You could fix this by adding more auxiliary columns and adding values on separate columns to make sure they don't overlap, but you would need a ridiculous amount of columns for this and we already tried it, it went horribly wrong. I know this request might not be clear so I'll reply to anyone that tries to help, other people struggled with this in the past and if I'm here it's because it's my last resort before giving up.

I have tried asking in the sheets Discord and I got told to come here many times to be fair but every new person that got involved with this found a new approach but we never managed to solve it. I ended up receiving a script that does this and it does the job but it's extremely slow. Even if you help me fix the script instead, I need to solve this for multiple formulas, as my sheet depends on this several times. Having that script run on edit was horrible for its performance.

I'm going to be posting here the script that does the job ONLY because I think it could help you understand the issue and NOT because I'm trying to fix the script. I'm trying to get rid of it.

Other setup:

Let me know!

UPDATE:

With the possibility of starting arrays again you can use:

=BYROW(SEQUENCE(COLUMNS(B1:1) INDEX(MAX(ROW(A:A)*(A:A<>"")))-ROW()),
LAMBDA(each,SUM(MAP(A1:A5,LAMBDA(arr,IFNA(IFERROR(INDEX(B1:5,ROW(arr),each row()-XLOOKUP(1,INDEX(--REGEXMATCH(A7:INDEX(A7:A,each),arr)),INDEX(ROW(A7:INDEX(A7:A,each))),-90,0,-1)))))))))

RE-UPDATE

For avoiding similar matching values, you can use this formula. Always separate with the same character, " " in my formula, or change it accordingly:

=BYROW(SEQUENCE(COLUMNS(B1:1) INDEX(MAX(ROW(A:A)*(A:A<>"")))-ROW()),
LAMBDA(each,SUM(MAP(A1:A5,LAMBDA(arr,IFNA(IFERROR(INDEX(B1:5,ROW(arr),each row()-XLOOKUP(1,BYROW(A7:INDEX(A7:A,each),LAMBDA(v,IF(IFERROR(MATCH(arr,TRIM(SPLIT(v," ")),0),)>0,1,0))),INDEX(ROW(A7:INDEX(A7:A,each))),-90,0,-1)))))))))

CodePudding user response：

Solution presented by the Spreadsheets Discord (Astral):

=ArrayFormula(lambda(keys,values,queries,lambda(lastrow,reduce(if(sequence(lastrow columns(values)-1),),sequence(lastrow),lambda(a,c,byrow({a,transpose(split(transpose(iferror(rept("