Forum Discussion
Cross sheet functionality and maintenance of the chronological order of a formula
Excel_Struggler The performance issue in this case is due to an array object (keyId) being passed to the lookup_array argument of XMATCH. If we spill the keyId results directly to a separate range in the worksheet, then reference the spill range in the lookup_array, it will drastically improve the calculation speed.
In the attached revision, I've defined the FILLDOWN function in Name Manager, moved the keyId formula to cell I6, and simplified the final formula in cell F6. I tested it to 16,000 rows and the calculation lag was virtually unnoticeable on my system (milliseconds). Hopefully it will perform well enough that you can avoid splitting the data into multiple sheets.
Cheers!
Excel_Struggler wrote:The solutions I can think of are:
1. Simplifying the formula making it less stress intensive so that it would be possible to merge all sub-sheets into one big sheet
This would be the way to go, in my opinion. Add another column with data validation to select the Week number, rather than having each week on a separate tab.
Regarding your description for "Order of Retrieval", I'm struggling to follow what you meant because your original formula doesn't seem to be doing exactly what you've described (to the best of my understanding). However, since your question doesn't appear to indicate that there's anything wrong with the formula, I made the assumption that it's already returning the desired results.
The following single-cell dynamic array formula returns the same results as your original formula(s), with the additional lookup criteria for week number:
=LET(r, 5, c, 3,
data, WRAPROWS(TOCOL(D6:F25), r*c),
array, A6:A25 & B6:B25 & C6:C25,
keyId, FILTER(array, NOT(MOD(SEQUENCE(ROWS(array),, 0), r))),
rowId, SEQUENCE(ROWS(keyId)),
row_nums, MAP(keyId, rowId, LAMBDA(v,n, XMATCH(v, TAKE(keyId, n-1),, -1))),
WRAPROWS(TOCOL(IFERROR(INDEX(data, row_nums, SEQUENCE(, r*c)), "")), c)
)
Sample Results
Note: I unmerged columns B to F in my sample file, so the Factor data is now referencing range D6:F25 instead of G1:I25.
This method assumes each item always has the same number of records (5 rows each). If the number of records is not consistent, however, a "fill down" approach is needed:
=LET(
FILLDOWN, LAMBDA(range, SCAN("", range, LAMBDA(a,v, IF(v="", a, v)))),
keyId, FILLDOWN(A6:A25) & FILLDOWN(B6:B25) & FILLDOWN(C6:C25) & "|" & SCAN(0, A6:A25, LAMBDA(a,v, IF(v="", a+1, 1))),
rowId, SEQUENCE(ROWS(keyId)),
row_nums, MAP(keyId, rowId, LAMBDA(v,n, XMATCH(v, TAKE(keyId, n-1),, -1))),
IFERROR(INDEX(D6:F25, row_nums, {1,2,3}), "")
)
EDIT: you may need to remove the concatenation of column B (week number) from the keyID array. See the v2 file attached, if the first file does not work as expected...
Hi djclements,
First and foremost, thank you for your detailed response.
Your solution and mine both function correctly in terms of retrieval order. However, I believe the explanation could have been clearer.
I tested your solutions, primarily the fill-down approach, given the inconsistent number of records in my workbook.
Unfortunately, with a sufficient number of rows, I still encounter performance issues using your solution, similar to what I experienced with mine. I tested around 8,000 rows.
I've attached a sample for you to test yourself.
Simply adding another column with data validation for the week is not a feasible solution for achieving the desired result in my workbook. My sample is highly simplified compared to my actual workbook, so it might give that impression. Assume a high number of rows in any case (about 8.000-15.000).
I'd love to hear if you have any other ideas to address my issue. I'm still inclined towards splitting the data into sub-sheets for simplicity and performance stability, but I'm unsure if this is technically feasible in Excel.
Cheers
Excel_Struggler The performance issue in this case is due to an array object (keyId) being passed to the lookup_array argument of XMATCH. If we spill the keyId results directly to a separate range in the worksheet, then reference the spill range in the lookup_array, it will drastically improve the calculation speed.
In the attached revision, I've defined the FILLDOWN function in Name Manager, moved the keyId formula to cell I6, and simplified the final formula in cell F6. I tested it to 16,000 rows and the calculation lag was virtually unnoticeable on my system (milliseconds). Hopefully it will perform well enough that you can avoid splitting the data into multiple sheets.
Cheers!
Wizardry!
The performance exceeded my expectations even with the additional formatting and formulas in my workbook.
Thank you so much for your assistance 🙂