Forum Discussion
How to generate a table of data built up off of smaller parts?
michyano If I've understood you correctly, I think the error can be solved by filtering the data first, to exclude any blank rows / rows that contain all zeros.
=LET(data, A2:E40, arr, FILTER(data, NOT(BYROW(data, LAMBDA(r, AND(r=0))))), DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET( acc, INDEX(arr, n, 4), top, SCAN(INDEX(arr, n, 2), IF(SEQUENCE(INDEX(arr, n, 5)), acc), LAMBDA(a,r, a+r)), name, INDEX(arr, n, 1), VSTACK(v, IFNA(HSTACK(name, top-acc, top), name))))), 1))As requested, the headers were also excluded in this example (data range starts at A2 instead of A1). An empty string was used as the initial_value for the REDUCE function, and the whole thing was wrapped in the DROP function to eliminate the first row from the results. Hopefully that works!
micyano Do you have Excel for MS365? If so, one possible solution is to use the REDUCE / VSTACK method, which iterates through the rows of the data range, performs the necessary calculations, then stacks the results together vertically.
=LET(data, A1:E17, arr, DROP(data, 1),
REDUCE(TAKE(data, 1, 3), SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
acc, INDEX(arr, n, 4),
top, SCAN(INDEX(arr, n, 2), IF(SEQUENCE(INDEX(arr, n, 5)), acc), LAMBDA(a,r, a+r)),
name, INDEX(arr, n, 1),
VSTACK(v, IFNA(HSTACK(name, top-acc, top), name))))))
For each row in the data range, the INDEX function is used to identify the values from each applicable column. The SCAN function is used to perform the primary calculation, which is to return the list of "Top" values in an array. The "Bottom" values are calculated simply by subtracting the accumulator (portion size) from the "Top" values. The IFNA and HSTACK functions are then used to stack the results together horizontally, along with the "Name". The results should look something like this:
MS365 Solution with Dynamic Array Functions
This is exactly what I needed, I could not have figured this out on my own thank you. You've saved me a lot of time I very much appreciate it! Now I need to dissect this whole thing to understand it. I haven't used any of these formulas before other than index. This is much more complicated of a code than I expected.
