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
Ok I've run into an issue trying to implement this into my worksheet. The data that are the inputs for the table are read from a dynamic table (of sorts, it is 40 rows high, with data usually in the top 10-20 rows, but at time can need more than 40 so the rows in that section of the table are copied and inserted into that portion of the workbook to expand the input table as to incorporate the associated calculations further down each row.)
If I try and select the entire 40 lines as the input table, I get an error. If doesn't seem to matter if the extra rows it reads are blanks, or 0's. Is there a way to ignore it? I'd like the user of the sheet to not have to worry about altering the ranges of the calculations after the size of the input is changed.
Also, not as important, I can work around it, but how do I stop it from adding the headings to the dynamic table? I tried changing the range to just not include the headings, but it then seems to skip the top section, (I suspect this has something to do with the DROP function). I'm starting to understand the individual functions, but the combinations of them is still beyond me. I suspect the name array that is being created is the titles for the columns, but I can't figure out how to remove that from the formula without everything breaking
Ok it's not that but I still can't figure it out what is "v", I'm having trouble understanding the LAMBDA function, it lets you define an equation as a function to use I think, but I don't understand how it works.
Thanks for your time
Michael
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!
Thanks so much! That works exactly!
Thank you everyone else who replied as well, you guys are awesome!
michyano Sorry, I missed your edit regarding a better understanding of the REDUCE / VSTACK method. It can be a tough one to get the hang of, for sure...
With the REDUCE function, SEQUENCE(ROWS(arr)) is used in the array parameter to generate a list of numbers from 1 to the total number of rows in arr. The n parameter defined in the LAMBDA function represents the current iteration number, as it loops through the sequence. If you're familiar with VBA For Next loops, it's similar to using:
For n = 1 to arr.Rows.Count 'calculate something Next nThe v parameter defined in the LAMBDA function represents the value (results) of the previous iteration. In the case of the first iteration, it refers to value specified in the intial_value parameter. The results of the calculations performed on each iteration are then stacked together with the previous results (v) using the VSTACK function. If you don't want the initial_value included in the final results, the DROP function is needed to remove it.
I hope that helps to better understand the logic. Cheers!
