A generalisation of the MAP lambda helper function

Patrick2788 

Sorry for off topic, tried your sample with Office Script

function main(workbook: ExcelScript.Workbook) {

    let width = 0; 
    const attributes = workbook.getActiveWorksheet()
        .getRange("attributes")
    const texts = attributes.getValues()
    const targetCell = attributes.getCell(0,1)

    texts.map(x => {
        let i = x.toString().match(/,/g).length;
        width = i > width ? i : width
    })

    const textsItems = texts.map( x => {
        let y = x.toString().split(",");
        y[width+1] = "";
        return y
    }) 

    const target = targetCell
        .getResizedRange(textsItems.length - 1, width + 1)
    const format: ExcelScript.RangeFormat = target.getFormat()

    target.setValues(textsItems)
    format.autofitColumns()
    format.setVerticalAlignment(ExcelScript.VerticalAlignment.center)

}

Patrick2788 

Thanks for the demonstration!   It achieves some of the goals I had in mind brilliantly.  It simultaneously demonstrates both the power of the function against a sizable problem and confirms its usability in that all the 'clever' machinations are discretely out of sight.

By using a defined name to hold your Lambda function, the worksheet formula can be made to appear even simpler

"Worksheet formula"
= MAPλ(attributes, SPLITCSV);

"where"
SPLITCSV
= LAMBDA(text, TEXTSPLIT(text, ", ", CHAR(10)))

 If only it worked that way with the native Excel function!

Finally getting to this one!

In re: Wyn's challenge.

Here's my take:

=LET(
    flat, UnPivotMλ(
        tblVOTES[VoterID],
        weight,
        tblVOTES[[Vote1]:[Vote3]]
    ),
    colors, TAKE(UNIQUE(flat), , -1),
    agg, SORT(GROUPBY(colors, colors, COUNTA, 0, 0), 2, -1),
    TAKE(agg, 4)
)

//UnPivotMλ being:

UnPivotMλ = LAMBDA(rows, columns, values,
        LET(
            i, ROWS(rows),
            j, COLUMNS(rows),
            c, COLUMNS(columns),
            s, TOCOL(SEQUENCE(, j) * SEQUENCE(c, , 1, 0)),
            row_labels, WRAPROWS(TOCOL(CHOOSECOLS(rows, s)), j),
            attribute, TOCOL(CHOOSEROWS(columns, SEQUENCE(i, , 1, 0))),
            v, TOCOL(values),
            HSTACK(row_labels, attribute, v)
        )
    )

PeterBartholomew1 Once again, great job on producing a generalized solution to the nested arrays issue. Hopefully Microsoft will take note of your efforts and come up with a built-in solution in the future.

Your reference to Oz_Sunray's live challenge caught my attention, as I too had come across that video in my YouTube feed. A lambda-free variant to avoid the array of arrays issue for that particular challenge could be:

=LET(
    names, UNIQUE(Votes[Ballot]),
    colors, INDEX(Votes, XMATCH(names, Votes[Ballot]), {2,3,4}),
    names_colors, TOCOL(names & "|" & colors),
    keys, UNIQUE(names_colors),
    weight, XLOOKUP(keys, names_colors, TOCOL(IF({1}, {3,2,1}, colors))),
    TAKE(SORT(HSTACK(Available, MMULT(N(Available = TOROW(TEXTAFTER(keys, "|"))), weight)), 2, -1), 4)
)

Note: the weight array can be adjusted as desired (e.g. {1,1,1} instead of {3,2,1}).

This is one situation where INDEX / XMATCH is preferred over XLOOKUP, as it is perfectly capable of returning multiple columns of data for multiple lookup values, whereas XLOOKUP is not.

While I typically seek out alternative methods to overcome the nested arrays issue, complex scenarios can quickly become convoluted, making it difficult for the average user to follow. Your MAPλ function seems to take care of that nicely. Thank you for sharing!

I have posted a further application of the MAPλ using the OP's (joelb95) workbook.  In the workbook I have applied the home-rolled helper function both to the task of maintaining multiple running totals and to a FIFO calculation of the same 'Lots'.

Re: Your thoughts serializing arrays to manage state in excel (by way of inventory tracking) - Microsoft Community Hub

The FIFO calculation filters the event table by each 'Lot' in turn but MAPλ stack the tables as an array.

As an alternative to the array of tables the user can select an array of grouped tables that focus on the outputs along with associated input costs.

A key element of both MAPλ and the FIFOλ functions is that they are much easier to use than they were to write.  But then, that is the whole idea of using modern methods and LAMBDA in particular!

Patrick2788 

Thanks for the preliminary feedback.  You are correct; it would be a large and somewhat indigestible meal taken at one sitting!  Its saving grace might be that it is possible to pick the small appetiser off the top without getting involved with anything too rich.

The Lambda helper function itself is intended to hide all its complexity from the user.  I wrote it as a stop-gap until such time as Microsoft get their act together and write versions of their helper functions that will generate arrays of arrays rather than error messages.  I understand Google Sheets does not have this limitation.

My idea in publishing the MAPλ helper function at this stage is to get others to try it.  All that is required is to write a function that generates an array and then use MAP to specify a repeated structure that will fail with the nested array error.  Then bring the MAPλ helper function into the AFE from GitHub and use IntelliSense autocomplete to select MAPλ in place of MAP.  Everything should just work with no further effort on behalf of the user.

These days most of the formulas I write today generate arrays of arrays in order to make best use of the spreadsheet grid.  Sometimes it is possible by using broadcasting but, as soon as a helper function is involved, things start to fall apart.  REDUCE/_STACK offers an understandable solution for smaller problems but it runs out of control as the problem size increases.

If you to try the function, let me know how you get on.  Did it deliver what you require and did it appear to be efficient?