Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
I remember JoeMcDaid stating that 2D arrays are the only data types held in Excel; rows columns and scalars are merely special cases with the last being a 1x1 array. So why is it such a proble...
PeterBartholomew1 I did think of a MAP solution just now to test out, and its really just a glorified INDEX solution, please try the below. I've changes MEMBERS lambda to TRANSPOSE rather than CONCAT, then MAP across 2 arrays, the row indices and column indices.
= LET(
\0, "Extract source data",
pos, --LEFT(rawdata,7),
name, (MID(rawdata,8,22)),
team, TRIM(MID(rawdata,31,26)),
time,--("00:"&RIGHT(rawdata,5)),
teamList, UNIQUE(team),
\1, "Build Lambda functions",
SCOREλ, LAMBDA(t,
LET(
pts, FILTER(pos, team=t),
SUM(INDEX(pts,{1,2,3,4}))
)),
MEMBERSλ, LAMBDA(t,
LET(
m, FILTER(name, team=t),
TRANSPOSE(m)
)),
\2, "Identify scoring members of teams in order",
teamScore, MAP(teamList, SCOREλ),
teamOrder, SORTBY(teamList, teamScore),
row_,MAKEARRAY(ROWS(teamOrder),4,LAMBDA(i,j,i)),
col_,MAKEARRAY(ROWS(teamOrder),4,LAMBDA(i,j,j)),
scoring, MAP(teamOrder, MEMBERSλ),
MAP(row_,col_,LAMBDA(r_,c_,INDEX(MEMBERSλ(INDEX(teamOrder,r_)),c_)))
)
I got some advice concerning the original 'array of arrays' problem. Essentially the advice was 'rather than trying to create arrays of arrays, reduce the array to 'thunks', which can be handled as scalars and evaluated at the end using MAKEARRAY. In the event, I didn't even need to do that because I was already working with Lambda functions (team name as the parameter) which evaluated correctly.
= LET(
teamList, UNIQUE(team),
teamScore, MAP(teamList, SCOREλ),
teamOrder, SORTBY(teamList, teamScore),
result, MAKEARRAY(COUNTA(teamList), 4,
LAMBDA(t,p,INDEX(MEMBERSλ(INDEX(teamOrder,t)), p))
),
IFERROR(result, ""))- That's a nice clean construction that encapsulates our journey from your original post!!