Data Structure Arrays with LAMBDA Functions in Excel

Work in Progress :: Please contact us to report errors, typos, etc.

This article proposes a method for working with what I'm calling a Data Structure Array in Excel or simply "Structure" for short. There may be an infinite number of ways to organize data within a 2-dimensional grid. So, this is just one possible method.

WHY? What is the Need? As I've been working with LAMBDA functions in Excel, I keep coming across the desire to (a) output more than one result, (b) store multiple arrays in a single variable and (c) work with arrays that have more than two or three dimensions.

The idea for this type of data structure stems mainly from my experience using the struct function for the "structure array" in MATLAB. There are similarities, but they do not function exactly the same way. The approach described here is based on the use and availability of certain Excel functions.

Sample Data Structure Arrays

The example below shows a very simple structure that consists of three named sets of data which we will call fields to stick with common terminology. "Title", "Chars" and "Numbers" are the names of these 3 fields. Color has been added only to help identify the main attributes of the structure.

Attributes of a Structure

Field Names: The left column of the structure is used only for the names of each field and must be unique because we'll be using the MATCH function in Excel to get fields from the structure.

Field Size: Columns 2 and 3 in the rows containing the field names are the SIZE of the element (ROWS x COLUMNS). A field can be a minimum of size 1x1 and a maximum determined by memory and array size limits in Excel (whatever that might be).

Data: The array defined by the field starts in the row immediately below the name and size in column 2.

Nested Structures

A field can itself be another structure, and there is no built-in limit to how many structures may be nested. You can refer to field "ii" within field "B" using the array {"B","ii"}. Or, if you are using indices for the names of the arrays, the array {2,5,3} would refer to field 3 within field 5 within field 2.

The example below shows a portion of what is essentially a 4-dimensional structure (x,y,z plus time, t) representing the surface z = x^2+y^2-t^2 for t={1,2,...}.

In the above example, field {2,"z"} would refer to the 3x3 matrix {-2,1,6;1,4,9;6,9,14}

Functions for Working with Structures

We may expand on this list over time, or continue to develop how these functions work, so the function is not provided on this page. Instead, you may download the sample file to access and use the following LAMBDA functions.

STRUCT_GET(name,structure) :: Retrieve a field by name from an existing structure
STRUCT_CREATE(name,value,[name_2],[value_2],...]) :: Create a structure with up to 4 name/value pairs.
STRUCT_APPEND(structure,name,value) :: Append a new field to an existing structure.
STRUCT_REMOVE(name,structure) :: Remove a field by name from an existing structure.

STRUCT_GET

=STRUCT_GET(name, structure)

This function retrieves a field from a structure by giving it the name of the field. The name should be the same data type (number or string) used for the field name in the structure.

In the Nested Structure example above, STRUCT_GET({1,"z"}) returns {1,4,9;4,7,12;9,12,17}. STRUCT_GET(2) returns the field named 2 which is the 12x4 structure containing fields "x","y","z".

▼ LAMBDA Function Code

/**
* Returns the field defined by the name
*/
STRUCT_GET = LAMBDA(name,structure,
LET(doc,"https://www.vertex42.com/lambda/structures.html",
    n,COLUMNS(name),
    getStruct,LAMBDA(id,struct_i,
        LET(
            row,MATCH(id,INDEX(struct_i,0,1),0),
            rows,INDEX(struct_i,row,2),
            cols,INDEX(struct_i,row,3),
            INDEX(struct_i,row+1,2):INDEX(struct_i,row+rows,1+cols)
        )
    ),
    IF(n=1,
        getStruct(name,structure),
        REDUCE(structure,SEQUENCE(n),LAMBDA(acc,i,
            getStruct(INDEX(name,i),acc)
        ))
    )
));

STRUCT_CREATE

=STRUCT_CREATE(name_1, value_1,...,[name_4],[value_4])

Create a structure by specifying up to 4 name/array pairs. You can add more via STRUCT_APPEND. The example shown in the first image above can be created using the following:

LAMBDA Function Code for AFE Workbook Module

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    STRUCT_CREATE("Title","Structure Example","Chars",chars,"Numbers",numbers)
)

▼ LAMBDA Function Code

/**
* Creates a structure with up to 3 fields.
*/
STRUCT_CREATE = LAMBDA(name_1,array_1,[name_2],[array_2],[name_3],[array_3],[name_4],[array_4],
LET(doc,"https://www.vertex42.com/lambda/structures.html",
    one,VSTACK(HSTACK(name_1,ROWS(array_1),COLUMNS(array_1)),HSTACK("",array_1)),
    include_2,NOT(AND(ISOMITTED(name_2),ISOMITTED(array_2))),
    include_3,NOT(AND(ISOMITTED(name_3),ISOMITTED(array_3))),
    include_4,NOT(AND(ISOMITTED(name_4),ISOMITTED(array_4))),
    two,IF(include_2,
        IFERROR(VSTACK(HSTACK(name_2,ROWS(array_2),COLUMNS(array_2)),HSTACK("",array_2)),""),
        "Empty"
    ),
    three,IF(include_3,
        IFERROR(VSTACK(HSTACK(name_3,ROWS(array_3),COLUMNS(array_3)),HSTACK("",array_3)),""),
        "Empty"
    ),
    four,IF(include_4,
        IFERROR(VSTACK(HSTACK(name_4,ROWS(array_4),COLUMNS(array_4)),HSTACK("",array_4)),""),
        "Empty"
    ),
    ret,IF(NOT(include_2),
        one,
        IF(NOT(include_3),
            VSTACK(one,two),
        IF(NOT(include_4),
            VSTACK(one,two,three),
            VSTACK(one,two,three,four)
        ))
    ),
    MAP(ret,LAMBDA(cell,IF(ISNA(cell),"",cell)))
));

STRUCT_APPEND

=STRUCT_APPEND(structure,name,value)

This function appends a new field to an existing structure. The first example above can be created by either calling STRUCT_APPEND in succession, or through nesting:

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    struct_1,STRUCT_CREATE("Title","Structure Example"),
    struct_2,STRUCT_APPEND(struct_1,"Chars",chars),
    struct_3,STRUCT_APPEND(struct_2,"Numbers",numbers),
    struct_3
)

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    STRUCT_APPEND(
        STRUCT_APPEND(
            STRUCT_CREATE("Title","Structure Example"),
            "Chars",chars
        ),
        "Numbers",numbers
    )
)

▼ LAMBDA Function Code

/** 
* Appends the new name and array to the existing structure.
*/
STRUCT_APPEND = LAMBDA(structure,name,array,
LET(doc,"https://www.vertex42.com/lambda/structures.html",
    ret,VSTACK(structure,
        HSTACK(name,ROWS(array),COLUMNS(array)),
        HSTACK("",array)
    ),
    MAP(ret,LAMBDA(cell,IF(ISNA(cell),"",cell)))
));

STRUCT_REMOVE

=STRUCT_REMOVE(name, structure)

This function will remove a field by name. It currently only removes a field from the first dimension of a nested structure. In other words, name can only be a single value.

▼ LAMBDA Function Code

/**
* Removes a field by name from an existing structure.
*/
STRUCT_REMOVE = LAMBDA(name,structure,
LET(doc,"https://www.vertex42.com/lambda/structures.html",
    row,MATCH(name,INDEX(structure,0,1),0),
    rows,INDEX(structure,row,2),
    filter_rows,VSTACK(SEQUENCE(row-1,1,1,0),
        SEQUENCE(rows+1,1,0,0),
        SEQUENCE(ROWS(structure)-(row+rows),1,1,0)
    ),
    FILTER(structure,filter_rows)
));

Examples

Example 1 - Create a 4-D Structure (x,y,z, plus time, t)

This example shows how the Nested Structure example above can be generated using a combination of other LAMBDA functions and a REDUCE loop.

Test: Copy and Paste this LET function into a cell
=LET(
    xvec, SE(1, 3),
    yvec, SE(1, 3),
    tvec, SE(1, 2),
    xgrid, MESHGRID(xvec, yvec, "X"),
    ygrid, MESHGRID(xvec, yvec, "Y"),
    construct, REDUCE("",tvec,
        LAMBDA(acc, t,
            LET(
                zgrid, xgrid ^ 2 + ygrid ^ 2 - t ^ 2,
                internal, STRUCT_CREATE("x", xgrid, "y", ygrid, "z", zgrid),
                IF(t=INDEX(tvec,1,1), STRUCT_CREATE(t, internal), STRUCT_APPEND(acc, t, internal))
            )
        )
    ),
    construct
)

Example 2: 2D X-Y Grid with Calculated Z

This example creates an X-Y Grid then calculates z = x^2+2*y^1.2-x*y

=LET(
    xvec, LINSPACE(1, 3, 5),
    yvec, LINSPACE(1, 4, 5),
    xgrid, MESHGRID(xvec, yvec, "X"),
    ygrid, MESHGRID(xvec, yvec, "Y"),
    zgrid, xgrid^2 + 2*ygrid^1.2 - xgrid*ygrid,
    STRUCT_CREATE(
        "X", xgrid,
        "Y", ygrid,
        "Z", zgrid
    )
)

Example 3: Store data for a plot

This example shows how you can store all the data for a 2D plot in a single structure. The purpose of these structures is to be able to pass them between other LAMBDA functions. However, this is an example of storing data within a structure.

=LET(
    xvec, LINSPACE(0, 2 * PI(), 10),
    yvec, SIN(xvec),
    STRUCT_CREATE(
        "Title", "Y = SIN(X)",
        "X", TRANSPOSE(xvec),
        "Y", TRANSPOSE(yvec)
    )
)

References & Resources

Matlab: struct function

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

Data Structure Arrays in Excel

Sample Data Structure Arrays

Attributes of a Structure

Nested Structures

Functions for Working with Structures

STRUCT_GET

▼ LAMBDA Function Code

STRUCT_CREATE

LAMBDA Function Code for AFE Workbook Module

▼ LAMBDA Function Code

STRUCT_APPEND

▼ LAMBDA Function Code

STRUCT_REMOVE

▼ LAMBDA Function Code

Examples

Follow Us On ...