Building arrays of arrays in Excel (take 2)

This video goes a step further, and shows how you can generate such an array of arrays using a single formula, so it will automatically expand as new data is added.

I walk through a few different examples, culminating in what I think of as the general solution, using REDUCE and VSTACK.

You can download a copy of the file I worked on in the video here:
(Note it's just the file exactly as it was at the end of the video - no comments or explanations included, so it won't make a lot of sense as a standalone, but hopefully will if you look at it with the video - I go through the tabs roughly left to right.)

Sections
00:00 Introduction and examples
04:16 Special cases
06:36 MAKEARRAY
11:58 SCAN / REDUCE introduction
17:18 General solution with REDUCE

[Reduce_Scan]

Thank you very much for this knowledge in the clip

[sunnybaggu]

Learning your content is very helpful. Thank you for sharing this.

[brunomerola]

Another great video, Diarmuid!

I have been using this "adjusted BYROW" LAMBDA below - and its equivalent BYCOL - to handle nested arrays situations.
It's very similar to the one in the video, with a general function fn and the required TAKE/DROP applied to the inputs.

BYROW = LAMBDA(array, fn,
LET(
vector, -- (COLUMNS(array) = 1),
start, fn (TAKE(array, 1)),
seq, DROP (SEQUENCE (ROWS (array));1),
result, REDUCE (start, seq, LAMBDA (acum, i,
VSTACK (
acum,
fn (INDEX (array i,
IFNA (result, "")))

[HauiUSA]

Hi Diarmuid, could you explain why in the last example a MAKEARRAY approach as illustrated in the previous problems wouldn't work as well? Thx

[peterbartholomew]

Hi Diarmuid
I listened to the video somewhat late in the day (like one month late (-: ). I couldn't resist the temptation to program along with your presentation.
Where I got to was a partial BYROW replacement

BYROWλ(array, fnλ)
= REDUCE(fnλ(TAKE(array, 1)), SEQUENCE(ROWS(array)-1, 1, 2),
LAMBDA(acc, k,
LET(
currentRow, CHOOSEROWS(array, k),
processedRow, fnλ(currentRow),
VSTACK(acc, processedRow)

)
)
)
with a couple of Lambda options for the problem-specific part of the problems:

HSortλ(row)
= SORT(row, , , 1)

TicketNumbersλ(row)
= LET(
ticketRange, INDEX(row, 3),
start, TEXTBEFORE(ticketRange, "-"),
end, TEXTAFTER(ticketRange, "-"),
SEQUENCE(1, 1 + end - start, start)
)

The worksheet formulas themselves looked rather less fierce
= BYROWλ(unsorted, HSortλ)
= IFNA(BYROWλ(ticketHoldings, TicketNumbersλ), "")

I think this is all rather similar to Owen's work and (now I see) almost identical to that of Bruno Merola (sorry, I hadn't gone through opening individual posts)

My point is that none of this should be necessary. BYROW (along with BYCOL, MAP, SCAN) should all do this by default. The helper functions, with the honourable exception of REDUCE have been poorly specified. When the whole purpose of a calculation is to return an array of arrays, "sorry Excel never has done that" simply is not good enough. Maybe you my have more influence than I do; at least another voice might help.

[Reduce_Scan]

I have a question about REDUCE,
Is there another way to extract all results without using VSTACK ?

[meniporat]

Thanks for an enlightening video.
Could you please upload the file?

[davidabuang]

I keep coming back to Wyn's ticket challenge whenever I find new, interesting methods. The latest of which uses TOCOL, IFS and SEQUENCE instead of iterative functions...

First, to repeat each Name based on the Num tix column, use:

=TOCOL(IFS(tix_3[Num tix]>=SEQUENCE(, MAX(tix_3[Num tix])), tix_3[Name]), 2)

Then, the same basic logic can be used to return the sequence of tickets for each name, with a few additional steps:

=LET(
low, --TEXTBEFORE(tix_3[Range], "-"),
upp, --TEXTAFTER(tix_3[Range], "-"),
start, MIN(low),
end, MAX(upp),
seq, SEQUENCE(, end-start+1, start),
TOCOL(IFS((seq>=low)*(seq<=upp), seq), 2)
)


IFS returns #N/A for any logical test that evaluates to FALSE, which works out perfectly with TOCOL's optional [ignore] argument set to 2 - Ignore errors. 🙂

[jerrydellasala]

Just found this, and the MAKEARRAY formula returns all #REF! errors. Any idea what changed or how to fix it?

[mistercary]

TextSplit does not work with BYROW(Lambda) in Excel for me, and I'm watching this 6 months later with excel 365 updated yesterday.
(Edit) Nevermind, I realize I should be using MAKEARRAY for TextSplit. BYROW only delivers a single column answer.
(Edit1) I found an alternative to makearray(textsplit) and all its index madness (report2):
report1, MAKEARRAY( ROWS( urh ), 3, LAMBDA( r, c, INDEX( TEXTSPLIT( INDEX( urh, r, 1 ), "@" ), c ) ) ),
report2, TEXTSPLIT( TEXTJOIN( "~", TRUE, urh), "@", " ~" )