1504168

/*Count of occurence and SumIfs From two different Column
@Author: Md.Ismail Hosen
@Email: [email protected] 
@Param: NameData : This is the column which has header text 
@Param: ValueData : This is the column from which we need to SumIf
*/
CountAndSumReport=LAMBDA(NameData,ValueData,
                        LET(
                            UniqueFirstData,UNIQUE(NameData),
                            CHOOSE({1,2,3},UniqueFirstData,COUNTIF(NameData,UniqueFirstData),SUMIF(NameData,UniqueFirstData,ValueData))

1504168

PROPERTIESCODEGENERATOR = LAMBDA(AllVariableDeclarationCode,[IsOnlyGetter],LET(
     AllPropertyCode, MAP(AllVariableDeclarationCode,LAMBDA(CurrentVariableDeclaration,PROPERTYCODEGENERATOR(CurrentVariableDeclaration,IsOnlyGetter))),
     Result,REDUCE("",AllPropertyCode,LAMBDA(FinalCode,CurrentPropertyCode,FinalCode & CHAR(10) & CHAR(10) & CurrentPropertyCode)),
     Result
    )
);

1504168

PROPERTYCODEGENERATOR = LAMBDA(VariableDeclarationLine,[IsOnlyGetter],LET(
   SplitVariableDeclarationLine, TRANSPOSE(TEXTSPLIT(VariableDeclarationLine," ")),
   VarNameIndex, MATCH("As",SplitVariableDeclarationLine,0)-1,
   NonObjectDataType, {"Integer";"Long";"Single";"Double";"Currency";"Date";"String";"Boolean";"Decimal";"Byte";"LongLong"},
   VarName, INDEX(SplitVariableDeclarationLine,VarNameIndex,1),
   VarDataType, INDEX(SplitVariableDeclarationLine,VarNameIndex+2,1),
   SetPrefix, IF(ISERROR(MATCH(VarDataType,NonObjectDataType,0)),"Set ",""),
   WithoutSetGetPropertyBodyLine, VarName & " = This." & VarName,
   PropertyAssignWithoutSet, "this." & VarName & " = "&VarName,
   EndPropertyCode, "End Property",

1504168

/*
Name:  Add2Cells
*/
Add2Cells = 
LAMBDA(Cell1,Cell2,LET(
   \\LambdaName, "Add2Cells",   

Cell1+Cell2
)
);

1504168

VALIDQUARTERFINDER = LAMBDA(QuarterData,
    LET(
        FindStartDate, LAMBDA(QuarterText,
            DATE(MID(QuarterText, 2, 4), RIGHT(QuarterText, 1) * 3 - 2, 1)
        ),
        FindQuarterText, LAMBDA(GivenDate,
            "Y" & YEAR(GivenDate) & "Q" & ROUNDUP(MONTH(GivenDate) / 3, 0)
        ),
        QuarterBetweenTwoDate, LAMBDA(QuarterData, Input2,
            LET(

1504168

1504168

1504168

//This will generate character sequence based on param. 
//Example case =CHARSEQUENCE(2, 2,"AA",2) will give an output like this ={"AA","AC";"AE","AG"}
CHARSEQUENCE =LAMBDA(NumberOfRow, NumberOfColumn, [StartChar], [Step],
    LET(
        _StartCol, IF(ISOMITTED(StartChar), 1, COLUMN(INDIRECT(StartChar & 1))),
        _StepCorrected, IF(ISOMITTED(Step), 1, Step),
        _Array, SEQUENCE(NumberOfRow, NumberOfColumn, _StartCol, _StepCorrected),
        _Result, MAP(
            _Array,
            LAMBDA(Curr,

1504168

// This function will filter a 2D Array for multiple column based on FilterForValues and comparision Criteria.
// ComparisionOperator can be : =,<>,>,<,>=,<= where default is =
// LogicalOperator can be : AND,OR where default is AND. Pass this param as text.
// IsHeaderPresent : Default is True
// You can pass same column twice.
// ColumnNameOrIndexes will be one row or column vector
// It can work on a 2D array not necessarily on Table only.
// Author: Md.Ismail Hosen([email protected])
FilterColumns=LAMBDA(TableData, ColumnNameOrIndexes, FilterForValues, [ComparisionOperator], [IsHeaderPresent], [LogicalOperator],
    LET(

1504168

' Format all the M code of Activeworkbook.
' Dependency: Microsoft XML, v6.0 (C:\Windows\System32\msxml6.dll)
' Developer: Md.Ismail Hosen
' Email : [email protected]
' Whatsapp: +8801515649307
' LinkedIn : https://www.linkedin.com/in/md-ismail-hosen-b77500135/
' Facebook : https://www.facebook.com/mdismail.hosen.7
' Youtube : https://www.youtube.com/channel/UCL-q7_WvISkw0Ox9FRBBzmw

Public Sub FormatActiveWorkbookMCode()