Forum Discussion
Working with Arrays of Ranges
Good day Peter,
While I cannot advocate for this type of table/data structure, I do appreciate the TRIMRANGE demonstration as it is still currently unavailable to me on the business channel. The concept of working with an array of ranges is also very useful and is something I would highly recommend to anyone looking to up their game.
There was a SUMIFS riddle posted on another forum a few weeks ago, and this concept worked beautifully for solving it. Even after broadcasting a vector of thunked ranges across multiple rows/columns, they still returned a range object when recalled later. I've attached a copy of the file if anyone's interested, which includes the link to my post explaining how it works. Incidentally, IFNA can also be used as a broadcasting tool instead of IF({1},...).
I tried to play around with your scenario a bit, but without TRIMRANGE at my disposal, I was forced to rewrite it using other methods.
Kind regards.
The way I see the problem in the attached workbook is that it can be solved with PIVOTBY but there are two obstacles:
- The data is already pivoted
- The row labels needed to be swapped
For both obstacles I have generalized Lambda functions: UnPivotMλ and FindReplaceλ. The solution is then:
=LET(
row_labels, FindReplaceλ(lstFruits, includes, lstBundles),
flat, UnPivotMλ(row_labels, dates, data),
r, TAKE(flat, , 1),
c, CHOOSECOLS(flat, 2),
v, TAKE(flat, , -1),
PIVOTBY(r, c, v, SUM, , , , 0)
)UnPivotMλ uses shaping functions and sequencing while FindReplaceλ is essentially a recursive REGEXREPLACE. I'd drop the code here, but I don't mean to derail Peter's post!