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!
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!
