Working with Arrays of Ranges

Nice idea with areas. In general we may avoid explicit markers and don't care about tables location. If only to keep existing layout for each table.

=LET(
  Tables,    (Table1,Table2,Table3,Table4),
  n,         SEQUENCE( AREAS(Tables) ),
  bar,       1000000,
  city,      LAMBDA(i, OFFSET(  TAKE(INDEX(Tables,,,i),1,1),-2,  ) ),
  qty,       LAMBDA(i, COUNTIF( DROP(INDEX(Tables,,,i), ,1), ">" & bar ) ),
  VSTACK(
    {"Countries","Cities > 1m"},
    HSTACK(
       MAP( n, city ),
       MAP( n, qty  )
    )
  )
)

and have not thousands of tables to care about performance.

Related to the DICTIONARYλ suggestion, I wonder if it might be worth defining a nestable dictionary such as, 

DICT = LAMBDA(array, LAMBDA([key],
    IF(ISOMITTED(key),
        array,
        XLOOKUP(key, VALUETOTEXT(TAKE(array, , 1)), TAKE(array, , -1))
        )
    )
);

which, like in python, returns a dictionary from a two column array. e.g. one could try

=countries()
=countries("United Kingdom")() 
=countries("France")("Paris")

where

countries =DICT(
    WRAPROWS(VSTACK(
       "United Kingdom", DICT(Table1),
       "Germany", DICT(Table2),
       "France", DICT(Table3),
       "Italy", DICT(Table4)
   ),2)
);

Though I'd still tend to side with the earlier PIVOTBY suggestion. 

Hi Lori

I think my experimentation may have more significance in terms of my working than I realised!

The pairing of a text string and a thunk allows me to build a data dictionary that provides a mechanism to pass complex data structures between Lambda function (in particular to return multiple data objects from a sub-function).

So far I have written a DICTIONARYλ constructor function that builds the data structure

/*  NAME:           DICTIONARYλ
    REVISIONS:      Date        Developer           Description  
                    Dec 19 2024 Peter Bartholomew   Original Development
*/
DICTIONARYλ 
= LAMBDA( _key01, object01, [_key02], [object02],  ...
    // Check inputs - Omitted required arguments
    Help?, OR(
        ISOMITTED(_key01),
        ISOMITTED(object01),
    ),

    NULLOMMITTED, LAMBDA(x, IF(ISOMITTED(x), "", x)),
    _key02, NULLOMMITTED(_key02),
    ...

    //  Procedure
    //  Build array of unique keys
        uniqueKeys, VSTACK(_key01, _key02,_...),
    
    //  Build array of thunked arrays (also applicable to scalars)
        objectArray, VSTACK(LAMBDA(object01), LAMBDA(object02), ...  ), 

    //  Combine unique keys with references to data objects
        result, FILTER(HSTACK(uniqueKeys, objectArray), uniqueKeys<>""),
       
    CHOOSE(Help? + 1, Result, Help)
    )
);

as well as generic and dictionary-specific functions to return data from dictionaries

GETλ
= LAMBDA(dict, LAMBDA(key,
    LET(
        uniqueKeys, TAKE(dict,,1),
        objects,    DROP(dict,,1),
        result,     XLOOKUP(key, uniqueKeys, objects, "keyword not found"),
        (@result)()
    )
));

GETSERIESDATAλ
= GETλ(seriesDataDictionary)
;

and the ability to return a list of keys

KEYSλ = LAMBDA(dict, TAKE(dict,,1));

Within a functional programming environment, each dictionary is write once, read many since all objects are immutable, but I should be able to combine two directories to give a fresh data object.

That might sound extravagant in terms of memory commitment, but since I am only storing thunks (references to memory allocated to a LET variable) the burden might be modest.

The Excel failure to return arrays of arrays makes it a little difficult to dump an image of the dictionary to the worksheet for checking or to provide persistence but I think my MAPλ function should work.

I have addressed these thoughts to you, as a Python programmer, in the hope that you might have additional insight that you could draw upon to educate me🙂!  Of course, others are welcome to contribute.

The way I see the problem in the attached workbook is that it can be solved with PIVOTBY but there are two obstacles:

1. The data is already pivoted
2. 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!

It would appear to do the job!

The Excel world is getting more confusing.  There always were those for whom the Excel grid was merely a space from which values could be read into VBA for processing and returned for display.  Then we were told that was to end, and everything would be OfficeScript.  Yet I see less focus on OfficeScript than on Python or PowerQuery M.  DAX also appears to be a somewhat niche artform. 

I really should broaden my skillset but at the moment it is an interesting challenge to see whether the Excel formula language can do every calculation that can be performed in Turing machines.  Python has NumPY and SciPY to support calculation but, for Excel formulas, one has to write one's own library of algorithms.  Whether one regards that as service to enhance Excel, or merely a waste of time replicating code that already exists elsewhere, is a matter for debate 🤔

In a sense, the array of 2D ranges down the sheet is a representation of a 3D range.  If we turn attention to 3D ranges of the form

range3D
= Sheet1:Sheet3!$K$3:$K$20

I suspect that life is going to get a whole lot trickier.  

It should be possible to return an array of thunked arrays, but that would not allow the use of TRIMRANGE or any of the xIFS functions.

= LET(
    TRIMARRλ, LAMBDA(column, LAMBDA(FILTER(column, NOT(ISBLANK(column)),""))),
    stacked,  HSTACK(range3D),
    thunkArr, TOCOL(BYCOL(stacked, TRIMARRλ)),
    MAP(thunkArr, LAMBDA(ϑ, AVERAGE(ϑ())))
  )

would apply the AVERAGE function to each sheet individually.  Another option might be to use INDIRECT which can return ranges.  For example

=INDEX(
    MAP(
        TOCOL(Sheet1:Sheet3!$A$1) & "!K3:K20",
        LAMBDA(string,
            LAMBDA(TRIMRANGE(INDIRECT(string)))
        )
    ),
    2,
    1
)( )

returns the trimmed range on sheet 2. 

Note: The range Sheet1:Sheet3!$A$1 contains the sheet names

= TEXTAFTER(CELL("filename",A1), "]")

Nothing too elegant but, as yet, we are simple sounding out the limits of what is possible, though I concede that may be limited by my lack of understanding.