Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 02, 2024
Solved

A generalisation of the MAP lambda helper function

Discussion topic.  Your thoughts are welcome.   On Saturday I finally bit the bullet and completed a MAPλ Lambda function that generalises the in-built MAP Lambda helper function.  As examples, I t...
excel
Formulas and Functions
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Sep 08, 2024

    This is a demonstration of the proposed MAP function used to solve a challenge provided by Oz_Sunray and Wyn Hopkins on data cleansing and vote counting.  The key element of my solution is to build a table of validated votes for each voter ID and each candidate (i.e. an array of arrays - much the same as any other problem of interest).  The formula is modularised using Lambda functions.  They are not that pretty, but neither are they impossible to follow.

    "Worksheet formula"
= AggregateVotesλ(
      tblVOTES[VoterID],
      tblVOTES[[Vote1]:[Vote3]],
      weight,
      tblAvailable[AVAILABLE]
  )

    which calls

    /*  "FUNCTION NAME:  AggregateVotesλ"
    "DESCRIPTION:    Aggregates all votes to candidate colors" */
/*  "REVISIONS:      Date            Developer           Description"
    "                08 Sep 2024     Peter Bartholomew   Original Development"
*/
AggregateVotesλ
= LAMBDA(voterID, votesCast, weight, available,
     LET(
        person, TOROW(UNIQUE(voterID)),
        votes,  MAPλ(person, AssignVotesλ(voterID, votesCast, weight, available)),
        count,  HSTACK(available, BYROW(votes, SUM)),
        return, TAKE(SORT(count, 2, -1), 4),
        return
     )
  );

    to build and aggregate the table of votes.  This, in turn, calls another Lambda function to clean and validate the votes corresponding to a single voterID

    /*  "FUNCTION NAME:  AssignVotesλ"
    "DESCRIPTION:   Validates and assigns votes to candidate colors for a single voterID " */
/*  "REVISIONS:      Date            Developer           Description"
    "                08 Sep 2024     Peter Bartholomew   Original Development"
*/
AssignVotesλ
= LAMBDA(voterID, ballots, weight, available,
    LAMBDA(p,
        LET(
            // "In the case of duplicate ballots return first"
            votesCast, XLOOKUP(p, voterID, ballots),
            // "Remove invalid and repeated votes"
            valid,     COUNTIFS(available, votesCast),
            distinct,  EXPAND(UNIQUE(FILTER(votesCast, valid), TRUE), , 3, ""),
            // "Assign votes to candidate colours"
            assigned,  BYROW((distinct = available) * weight, SUM),
            assigned
        )
    )
);

    I took the problem from Wyn's recording

    https://www.youtube.com/watch?v=FIcxyLqzWcE

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 12, 2024

PeterBartholomew1 

I'm having some fun with this function! A simple text to columns split with a formula. I know it's easy to do with the feature text to columns or PQ but it's more fun with formulas where I don't need to cobble together a solution with MAKEARRAY or resort to mischief with REDUCE. It's also much faster than a recursive function I built a while ago.

 

=LET(
    split, LAMBDA(texts, TEXTSPLIT(texts, ", ")),
    MAPλ(attributes, split)
)

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 04, 2024

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)

}

Resources