Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL LAMBDA Reporting with Arrays

Last active January 10, 2025 04:30
Show Gist options
  • Save CHatmaker/cc0e8975d30b40734641a06dbae02143 to your computer and use it in GitHub Desktop.
Save CHatmaker/cc0e8975d30b40734641a06dbae02143 to your computer and use it in GitHub Desktop.
Download ZIP
5G functions for Excel for Reporting with Arrays
Raw
BXL LAMBDA Reporting with Arrays
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Mar 17 2023 Craig Hatmaker Copyright
Apr 17 2023 Craig Hatmaker Reorganizing and adding help
Jul 03 2023 Craig Hatmaker See nFoldCartProdλ and PriorDimProdλ
Jul 11 2023 Craig Hatmaker See UnPivotDetailλ
Feb 11 2024 Craig Hatmaker See UnPivotDetailλ
*/
Aboutλ = TRIM(TEXTSPLIT(
"About: →BXL's Reporting with arrays module. Suggested module name: BXR¶" &
"Version: →Mar 23 2023¶" &
"Gist URL: →https://gist.github.com/CHatmaker/cc0e8975d30b40734641a06dbae02143 ¶" &
"Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"Function →Description¶" &
"Aboutλ →Produces this table¶" &
"SumColumnsλ →Totals an array's columns¶" &
"SumRowsλ →Totals an array's rows¶" &
"SumGroupsλ →Summarize a crosstab array's values where row in crosstab has a ¶" &
" →group code (ex. an Account #) that matches a column of group codes¶" &
" →(ex. a list of account #s to include)¶" &
"SumPeriodsλ →Totals a range containing values by period start and end dates¶" &
"SumGroupsAndPeriods, →Produces a crosstab array summarizing amounts in a table by Group and Period¶" &
"GetRowλ →Get a single row, and optionally the first n columns of that row from an array¶" &
"GetColumnλ →Get a single column, and optionally the first n rows of that column from an array¶" &
"UnPivotλ →Convert a crosstab array into a tabular array¶" &
"UnPivotDetailλ →UnPivotλ with more options. This converts a crosstab array into a tabular array¶" &
"nFoldCartProdλ →(n-Fold Cartesian Product) Create indexes for all permutations of n ¶" &
" →dimensions with various lengths (for multidimensional modeling).¶" &
"RevHrzArrayλ →Reverse Horizontal Array¶" &
"PriorDimProdλ →Product of Prior Dimension Counts¶" &
"RemoveZeroValuesλ →Remove zero values from an unpivoted array¶" &
"RunningTotalInRowsλ →Create running totals in of array's rows¶" &
"ReportGroupSummaryλ →Total detail values by group, subtract offset value totals, ¶" &
" →display subtotal¶" &
"ReportGroupDetailOffsetλ →List details by group, subtotal detail, subtract offset, ¶" &
" →subtotal group, display running total",
"→",
"¶"
)
);
/* FUNCTION NAME: SumColumnsλ
DESCRIPTION:*//**Totals an array's columns*/
/* REVISIONS: Date Developer Description
Mar 06 2023 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Added Help
*/
SumColumnsλ = LAMBDA(
// Parameter Declarations
[Array],
// Procedure
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Creates totals for each of an array's columns.¶" &
"VERSION: →Apr 10 2023¶" &
"PARAMETERS:→¶" &
"Array →(Required) A two dimensional array/range containing values to be summed.¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"35,40,45 →=SumColumnsλ(SEQUENCE(5,3))" ,
"→", "¶" )
),
// Check inputs
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!, Array),
// Procedure
Result, BYCOL(Array, LAMBDA(Column, SUM(Column))),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: SumRowsλ
DESCRIPTION:*//**Totals an array's Rows*/
/* REVISIONS: Date Developer Description
Mar 06 2023 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Added Help
*/
SumRowsλ = LAMBDA(
// Parameter Declarations
[Array],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Creates totals for each row in array.¶" &
"→¶" &
"VERSION: →Apr 10 2023¶" &
"PARAMETERS:→¶" &
"Array →(Required) A two dimensional array/range " &
"containing values to be summed.¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"10 →=SumRowsλ({1,2,3,4;4,3,2,1;10,20,30,40})¶" &
"10→¶" &
"100→",
"→", "¶" )
),
// Check inputs
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!, Array),
// Procedure
Result, BYROW(Array, LAMBDA(Row, SUM(Row))),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: SumGroupsλ
DESCRIPTION:*//**Creates an array of one row per group code totalling rows in a values array
where each row in the values array is for a single group code and many value rows can belong to the same group code*/
/* REVISIONS: Date Developer Description
Mar 06 2023 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Add Help
*/
SumGroupsλ = LAMBDA(
// Parameter Declarations
[Values],
[GroupColumn],
[GroupCodes],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Creates an array of value totals by group code.¶" &
"→where each values row for one group code.¶" &
"→and many rows can belong to the same group code.¶" &
"VERSION: →Apr 10 2023¶" &
"PARAMETERS:→¶" &
"Values →(Required) An array/range containing values to be summed.¶" &
"GroupColumn →(Required) A column containing a group code for each row in values.¶" &
"GroupCodes →(Optional) A list of unique group codes to include in result.¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"05,07,09 →SumGroupsλ({1,2,3;4,5,6;7,8,9;10,11,12},¶" &
"07,08,09 → {""A"";""A"";""B"";""C""}))¶" &
"10,11,12 →",
"→", "¶" )
),
// Check inputs
Values, IF(OR(ISOMITTED(Values), Values=""), #Value!, Values),
GroupColumn, IF(OR(ISOMITTED(GroupColumn), GroupColumn=""), #Value!, GroupColumn),
GroupCodes, IF(OR(ISOMITTED(GroupCodes), GroupCodes=""), Unique(GroupColumn), GroupCodes),
// Procedure
Result, MAKEARRAY(
ROWS(GroupCodes),
COLUMNS(Values),
LAMBDA(Row, Col,
LET(Group, INDEX(GroupCodes, Row),
Match, N(GroupColumn = Group),
Total, SUM(Match * INDEX(Values, , Col)),
Total
)
)
),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: SumPeriodsλ
DESCRIPTION:*//**Groups and totals all columns in a Values array by period resulting in one column for each period*/
/* REVISIONS: Date Developer Description
Mar 04 2022 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Remove SUMIFS to be compatible with arrays. Added Help.
*/
SumPeriodsλ = LAMBDA(
// Parameter Declarations
[Values],
[Dates],
[PeriodStarts],
// Procedure
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Groups and totals all columns in a Values array by period resulting in one column for each period*¶" &
"→into one column for each period. ¶" &
"→¶" &
"PARAMETERS:→¶" &
"Values →(Required) A two dimensional array/range containing values to be summed where" &
"each column is for a specific day¶" &
"Dates →(Required) A row of dates providing the date for each Values column¶" &
"PeriodStarts →(Required) A row of period start dates¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"03, 07 →=SumPeriodsλ({1,2,3,4;4,3,2,1;10,20,30,40}, {1,2,3,4}, {1,3})¶" &
"07, 03 →¶" &
"30, 70 →",
"→", "¶" )
),
// Check inputs
Values, IF(OR(ISOMITTED(Values), Values=""), #Value!, Values),
Dates, IF(OR(ISOMITTED(Dates), Dates=""), #Value!, Dates),
PeriodStarts, IF(OR(ISOMITTED(PeriodStarts), PeriodStarts=""), #Value!, PeriodStarts),
// Named Formulas
PrdNum, MATCH(Dates, PeriodStarts, 1),
// Procedure
Result, MAKEARRAY(
ROWS(Values),
COLUMNS(PeriodStarts),
LAMBDA(
Row,
Col,
SUM(INDEX(Values, Row, ) * (Col = PrdNum))
)
),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: SumGroupsAndPeriodsλ
DESCRIPTION:*//**Groups and totals all values from a table by group and period*/
/* REVISIONS: Date Developer Description
Mar 04 2022 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Remove SUMIFS to be compatible with arrays. Added Help.
*/
SumGroupsAndPeriodsλ = LAMBDA(
// Parameter Declarations
[ValuesColumn],
[GroupsColumn],
[DatesColumn],
[GroupCodes],
[PeriodStarts],
[PeriodEnds],
// Procedure
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Summarize values in a column by group codes in a second column ¶" &
"→and dates in a third into a crosstab array where each period's values ¶" &
"→are in a column and each groups values are in a row.¶" &
"PARAMETERS:→¶" &
"ValuesColumn →(Required) Column of values to be summed¶" &
"GroupsColumn →(Required) Column of group codes for each value¶" &
"DatesColumn →(Required) Column of dates for each value¶" &
"GroupCodes →(Required) Column of unique group codes to include¶" &
"PeriodStarts →(Required) Row of period start dates¶" &
"PeriodStarts →(Required) Row of period end dates¶" &
"→¶" &
"PROBLEM SOLVING:→¶" &
"Issue →What to try¶" &
"Nothing shows →Make sure the GroupsColumn's values are of the same type (numbers, text, or dates) as the GroupCodes' values¶" &
"→Make sure the ValuesColumn's values do not contain blanks (must be zeroes)¶" &
"→Make sure the DatesColumn are dates and not text values that look like dates¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"900,900 →=SumGroupsAndPeriodsλ({100;200;300;400;500;500;700;800}, ¶" &
"200,500 →{""A"";""B"";""C"";""A"";""B"";""A"";""C"";""A""}, {1;2;3;4;4;3;2;1}, ,{1,3}, {2,4})¶" &
"700,300 →",
"→", "¶" )
),
// Check inputs
ValuesColumn, IF(OR(ISOMITTED(ValuesColumn), ValuesColumn=""), 0, ValuesColumn),
GroupsColumn, IF(OR(ISOMITTED(GroupsColumn), GroupsColumn=""), 0, GroupsColumn),
DatesColumn, IF(OR(ISOMITTED(DatesColumn), DatesColumn=""), 0, DatesColumn),
GroupCodes, IF(OR(ISOMITTED(GroupCodes), GroupCodes=""), Unique(GroupsColumn), GroupCodes),
PeriodStarts, IF(OR(ISOMITTED(PeriodStarts), PeriodStarts=""), #Value!, PeriodStarts),
PeriodEnds, IF(OR(ISOMITTED(PeriodEnds), PeriodEnds=""), #Value!, PeriodEnds),
// Procedure
Result, MAKEARRAY(
ROWS(GroupCodes),
COLUMNS(PeriodStarts),
LAMBDA(
Row,
Col,
SUM(ValuesColumn *
(INDEX(GroupCodes, Row) = GroupsColumn) *
(DatesColumn >= INDEX(PeriodStarts, 1, Col)) *
(DatesColumn <= INDEX(PeriodEnds, 1, Col))
)
)
),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: GetRowλ
DESCRIPTION:*//**Get a single row, and optionally the first n columns of that row from an array*/
/* REVISIONS: Date Developer Description
Mar 15 2023 Craig Hatmaker Original Development
*/
GetRowλ = LAMBDA(
// Parameter Declarations
[Array],
[Row],
[Columns],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Get a single row, and optionally the first n columns of that row from an array¶" &
"→¶" &
"PARAMETERS:→¶" &
"Array →(Required) Array from which to extract a row¶" &
"Row →(Required) Row number to extract¶" &
"Columns →(Optional) If included, returns only the first n columns of the row¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"11,12,13 →=GetRowλ(Sequence(10,5), 3)",
"→", "¶" )
),
// Check inputs
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!, Array),
Row, IF(OR(ISOMITTED(Row), Row=""), #Value!, Row),
Columns, IF(VALUE(Columns) = 0, COLUMNS(Array), Columns),
// Procedure
Result, DROP(TAKE(Array, Row, Columns), Row - 1),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: GetColumnλ
DESCRIPTION:*//**Get a single column, and optionally the first n rows of that column from an array*/
/* REVISIONS: Date Developer Description
Mar 15 2023 Craig Hatmaker Original Development
*/
GetColumnλ = LAMBDA(
// Parameter Declarations
[Array],
[Column],
[Rows],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Get a single column, and optionally the first n rows of that column from an array¶" &
"PARAMETERS:→¶" &
"Array →(Required) Array from which to extract a column¶" &
"Column →(Required) Column number to extract¶" &
"Rows →(Optional) If included, returns only the first n rows of the column¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"3,13,23 →=GetColumnλ(Sequence(5,10), 3)",
"→", "¶" )
),
// Check inputs
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!, Array),
Column, IF(OR(ISOMITTED(Column), Column=""), #Value!, Column),
Rows, IF(VALUE(Rows) = 0, ROWS(Array), Rows),
// Procedure
Result, DROP(TAKE(Array, Rows, Column), , Column - 1),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: UnPivotλ
DESCRIPTION:*//**Convert a crosstab array into a tabular array;*/
/* REVISIONS: Date Developer Description
Mar 03 2023 Craig Hatmaker Original Development
*/
UnPivotλ = LAMBDA(
// Parameter Declarations
[Array],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Convert a crosstab array into a tabular array¶" &
"PARAMETERS:→¶" &
"Array →(Required) A two dimensional array with repeating elements going ¶" &
"→across the top, like dates, and non-repeating items going down ¶" &
"→the first column. In the matrix are values for the intersection ¶" &
"→of the repeating elements and items.¶" &
"→¶" &
"EXAMPLES: →=Unpivotλ(Sequence(5,10))",
"→", "¶" )
),
// Check Parameters
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!, Array),
// Named Formulas
idxArray, nFoldCartProdλ(HSTACK(COLUMNS(Array) - 1, ROWS(Array) - 1)),
idxTop, CHOOSECOLS(idxArray, 1),
idxLeft, CHOOSECOLS(idxArray, 2),
Headers, CHOOSEROWS(Array, 1),
Column1, INDEX(Array, idxLeft + 1, 1),
Column2, INDEX(Headers, idxTop + 1),
Column3, INDEX(Array, idxLeft + 1, idxTop + 1),
// Final Calculations
Result, HSTACK(Column1, Column2, Column3),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: UnPivotDetailλ
DESCRIPTION:*//**UnPivotλ with more options. This converts a crosstab array into a tabular array;*/
/* REVISIONS: Date Developer Description
Jul 11 2023 Craig Hatmaker Copyright
Feb 11 2024 Craig Hatmaker Fixed bug reported by Nicholas Hay
*/
UnPivotDetailλ = LAMBDA(
// Parameter Declarations
[ResultHeadings],
[AttributesArray],
[RepeatingHeadings],
[RepeatingValues],
[RemoveZeros],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →UnPivotλ with more options. This converts a crosstab array into a tabular array¶" &
"PARAMETERS:→¶" &
"ResultHeadings →(Required) A horizontal array or csv string of headings for the final result ¶" &
"AttributesArray →(Required) Usually this is the left most column in the crosstab array.¶" &
"→However, this array can contain more than one column to apply to each repeating value¶" &
"RepeatingHeadings →(Required) A horizontal array of headings for repeating values. This often contains dates¶" &
"RepeatingValues →(Required) A two dimentional array of values associated with attributes on the left and¶" &
"→headings across the top of the crosstab array¶" &
"RemoveZeros →(Optional) If TRUE (default) rows with zero for the value's column will be removed.¶" &
"→¶" &
"EXAMPLES: →=UnPivotDetailλ(""Attribute,Period,Amount"", Sequence(5,1), Sequence(1,10), Sequence(5,10))",
"→", "¶" )
),
// Check Parameters
ResultHeadings, IF(OR(ISOMITTED(ResultHeadings), ResultHeadings=""), #Value!,
IF(COLUMNS(ResultHeadings) = 1,
TEXTSPLIT(ResultHeadings, ","),
ResultHeadings
)
),
AttributesArray, IF(ISOMITTED(AttributesArray), #Value!, AttributesArray),
RepeatingHeadings, IF(ISOMITTED(RepeatingHeadings), #Value!, RepeatingHeadings),
RepeatingValues, IF(ISOMITTED(RepeatingValues), #Value!, RepeatingValues),
RemoveZeros, IF(OR(ISOMITTED(RemoveZeros), RemoveZeros=""), TRUE, RemoveZeros),
// Named Formulas
// idxArray, nFoldCartProdλ(HSTACK(COLUMNS(RepeatingValues) - 1, ROWS(RepeatingValues) - 1)),
idxArray, nFoldCartProdλ(HSTACK(COLUMNS(RepeatingValues) , ROWS(RepeatingValues) )),
idxTop, CHOOSECOLS(idxArray, 1),
idxLeft, CHOOSECOLS(idxArray, 2),
Attributes, CHOOSEROWS(AttributesArray, idxLeft),
Headers, INDEX(RepeatingHeadings, idxTop),
Values, INDEX(RepeatingValues, idxLeft, idxTop),
// Final Calculations
Table, VSTACK(ResultHeadings, HSTACK(Attributes, Headers, Values)),
Result, IF(RemoveZeros, FILTER(Table, CHOOSECOLS(Table, -1) <> 0), Table),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: nFoldCartProdλ
DESCRIPTION:*//**(n-Fold Cartesian Product) Create indexes for all permutations of n dimensions with
various lengths (for multidimensional modeling)*/
/* REVISIONS: Date Developer Description
Mar 03 2023 Craig Hatmaker Original Development
Jul 03 2023 Craig Hatmaker Made 100% self contained
*/
nFoldCartProdλ = LAMBDA(
// Parameter Declarations
[DimensionLengths],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →The n-Fold Cartesian Product creates indexes for all permutations of¶" &
"→n dimensions with each dimension having a (potentially) different length¶" &
"PARAMETERS:→¶" &
"DimensionLengths→(Required) A CSV text or horizontal array containing each dimension's length¶" &
"→¶" &
"EXAMPLES: →=nFoldCartProdλ({3,5,4})¶" &
"→=nFoldCartProdλ(""3,5,4"")",
"→", "¶" )
),
// Check Parameters
Array, IF(OR(ISOMITTED(DimensionLengths), DimensionLengths=""), #Value!,
IF(COLUMNS(DimensionLengths) = 1,
--TEXTSPLIT(DimensionLengths, ","),
DimensionLengths
)
),
// Create an array containing the product of prior dimensions
Columns, COLUMNS(Array),
NewArray, HSTACK(Array, 1),
PriorDimProd, MAKEARRAY(1, Columns,
LAMBDA(R, C,
PRODUCT(DROP(NewArray, 0, C))
)
),
// Create tuple counter
Tuple, SEQUENCE(PRODUCT(Array)),
// This repeats an element by the product of prior dimensions
Repitition, QUOTIENT(Tuple - 1, PriorDimProd),
// This calculates the element's index number
Result, 1 + MOD(Repitition, Array),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: RevHrzArrayλ
DESCRIPTION:*//**Reverse Horizontal Array (used by nFoldCartProd)*/
/* REVISIONS: Date Developer Description
Mar 03 2023 Craig Hatmaker Original Development
*/
RevHrzArrayλ = LAMBDA(
// Parameter Declarations
[HorizontalArray],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Reverse Horizontal Array (used by nFoldCartProd)¶" &
"PARAMETERS:→¶" &
"HorizontalArray→(Required) a horizontal arra¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"4,5,3 →=RevHrzArrayλ({3,5,4})",
"→", "¶" )
),
// Check Parameters
HorizontalArray, IF(OR(ISOMITTED(HorizontalArray), HorizontalArray=""), #Value!,
IF(COLUMNS(HorizontalArray) = 1, TextSplitλ(HorizontalArray), HorizontalArray)),
// Final Calculations
Elements, COLUMNS(HorizontalArray),
Result, INDEX(HorizontalArray, 1, SEQUENCE(1, Elements, Elements, -1)),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: PriorDimProdλ
DESCRIPTION:*//**Product of Prior Dimension Counts (used by nFoldCartProd)*/
/* REVISIONS: Date Developer Description
Mar 03 2023 Craig Hatmaker Original Development
Jul 03 2023 Craig Hatmaker Made 100% self contained
*/
PriorDimProdλ = LAMBDA(
// Parameter Declarations
[HorizontalArray],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Used by nFoldCartProd to calculate product of prior dimensions¶" &
"PARAMETERS:→¶" &
"HorizontalArray →(Required) a horizontal arra¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"6,3,1 →=PriorDimProdλ({3,5,4})",
"→", "¶" )
),
// Check Parameters
Array, IF(OR(ISOMITTED(HorizontalArray), HorizontalArray=""), #Value!,
IF(COLUMNS(HorizontalArray) = 1,
--TextSplit(HorizontalArray, ","),
HorizontalArray)),
// Function starts here
Columns, COLUMNS(Array),
NewArray, HSTACK(Array, 1),
Result, MAKEARRAY(1, Columns,
LAMBDA(R, C,
PRODUCT(DROP(NewArray, 0, C))
)
),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: RemoveZeroValuesλ
DESCRIPTION:*//**Removes rows in a 2 dimensional where the last column = 0
This was created to filter 0 values from UnPivotλ's results*/
/* REVISIONS: Date Developer Description
Mar 04 2023 Craig Hatmaker Original Development
Jul 11 2023 Craig Hatmaker Changed column to last column.
*/
RemoveZeroValuesλ = LAMBDA(
// Parameter Declarations
[Array],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Removes rows in an array where the 3rd column = 0. Used to filter 0 values from UnPivotλ's results¶" &
"PARAMETERS:→¶" &
"Array →(Required) An array with at least 3 columns",
"→", "¶" )
),
// Check Parameters
Array, IF(OR(ISOMITTED(Array), Array=""), #Value!,
IF(COLUMNS(Array) = 1, TextSplitλ(Array), Array)),
// Procedure
Result, FILTER(Array, CHOOSECOLS(Array, -1) <> 0),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: RunningTotalInRowsλ
DESCRIPTION:*//**Creates an array of running totals of values from another array*/
/* REVISIONS: Date Developer Description
Mar 04 2023 Craig Hatmaker Original Development
*/
RunningTotalInRowsλ = LAMBDA(
// Parameter Declarations
[Array],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Creates an array of running totals of values from another array¶" &
"PARAMETERS: →¶" &
"Array →(Required) An array of values¶" &
"EXAMPLE: →¶" &
"Result →Formula¶" &
"1, 3, 6; →=RunningTotalInRowsλ(SEQUENCE(3,3))¶" &
"4, 9, 15; →¶" &
"7, 15, 24; →",
"→", "¶" )
),
// Check Parameters
Array, IF(ISOMITTED( Array), #Value!, Array),
// Procedure
Rows, ROWS(Array),
Columns, COLUMNS(Array),
Result, MAKEARRAY(Rows, Columns,
LAMBDA(Row, Col,
SUM( TAKE( CHOOSEROWS( Array, Row), 1, Col))
)
),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: TextSplitλ
DESCRIPTION:*//**Converts TEXTSPLIT's text to numbers where appropriate*/
/* REVISIONS: Date Developer Description
Apr 20 2023 Rick Rothstein Original Development
*/
TextSplitλ = LAMBDA(
// Parameter Declarations
[String],
[Delimiter],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Converts TEXTSPLIT's text to numbers where appropriate¶" &
"PARAMETERS:→¶" &
"String →(Required) a CSV string to split¶" &
"Delimiter →(Optional) a comma is the default¶" &
"→¶" &
"EXAMPLES:→¶" &
"Result →Formula¶" &
"A,B,C →=TextSplitλ(""A,B,C"")¶" &
"1,2,3 →=TextSplitλ(""1,2,3"")",
"→", "¶" )
),
// Check Parameters
String, IF(OR(ISOMITTED(String), String=""), #Value!, String),
Delimiter, IF(OR(ISOMITTED(Delimiter), Delimiter=""), ",", Delimiter),
// Final Calculations
Array, TEXTSPLIT(String, Delimiter),
Result, IFERROR(0 + Array, Array),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: ReportGroupSummaryλ
DESCRIPTION:*//**Report created for tracking budget against spend. In that context, Group is an account, Detail are budgeted line items and Offset is the sum of spending for period. The basic layout is:
Group
Detail Subtotal Row
Offset Subtotal Row
Subtotal Row (Detail + Offset) */
/* REVISIONS: Date Developer Description
Mar 15 2023 Craig Hatmaker Original Development
*/
ReportGroupSummaryλ = LAMBDA(
// Parameter Declarations
[GroupIDColumn],
[GroupDescColumn],
[SubGroupTotals1],
[SubGroupTotals2],
[SubGroupTotals3],
[GroupHeading],
[PeriodHeadings],
[SubGroupLabel1],
[SubGroupLabel2],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Places totals in a report format. This was designed with budgets in mind where:¶" &
"→Groups are accounts and under each account are three sub groups:¶" &
"→SubGroup 1 is budgeted expenditures for an account,¶" &
"→SubGroup 2 is actual expenditures for an account and ¶" &
"→SubGroup 3 is balances for an account.¶" &
"LAYOUT:→¶" &
"Group Heading →Period headings¶" &
"Group ID and Description→Sub Group 1's Label then totals by period.¶" &
"→Sub Group 2's Label then totals by period.¶" &
"→'Subtotal' then Sub Group 3's totals by period.¶" &
"PARAMETERS:→¶" &
"GroupIDColumn →(Required) Column holding group IDs identifying which group a row's totals belongs to¶" &
"GroupDescColumn→(Required) Column holding group descriptions¶" &
"SubGroupTotals1→(Required) An array (e.g. Budgeted) where each row contains the group's totals by period ¶" &
"SubGroupTotals2→(Required) An array (e.g. Actuals) where each row contains the group's totals by period ¶" &
"SubGroupTotals3→(Required) An array (e.g. Balances) where each row contains the group's totals by period ¶" &
"GroupHeading →(Required) A label for group IDs (e.g. 'Account')¶" &
"PeriodHeadings →(Required) An array of period headings for each value's column¶" &
"SubGroupLabel1 →(Required) Group 1's row lable (e.g. 'Budgeted')¶" &
"SubGroupLabel2 →(Required) Group 2's row lable (e.g. 'Less Expense')",
"→", "¶" )
),
// Check Parameters
GroupIDColumn, IF(OR(ISOMITTED(GroupIDColumn), GroupIDColumn=""), #Value!, GroupIDColumn),
GroupDescColumn,IF(OR(ISOMITTED(GroupDescColumn), GroupDescColumn=""), #Value!, GroupDescColumn),
SubGroupTotals1,IF(OR(ISOMITTED(SubGroupTotals1), SubGroupTotals1=""), #Value!, SubGroupTotals1),
SubGroupTotals2,IF(OR(ISOMITTED(SubGroupTotals2), SubGroupTotals2=""), #Value!, SubGroupTotals2),
SubGroupTotals3,IF(OR(ISOMITTED(SubGroupTotals3), SubGroupTotals3=""), #Value!, SubGroupTotals3),
GroupHeading, IF(OR(ISOMITTED(GroupHeading), GroupHeading=""), #Value!, GroupHeading),
PeriodHeadings, IF(OR(ISOMITTED(PeriodHeadings), PeriodHeadings=""), #Value!, PeriodHeadings),
SubGroupLabel1, IF(OR(ISOMITTED(SubGroupLabel1), SubGroupLabel1=""), #Value!, SubGroupLabel1),
SubGroupLabel2, IF(OR(ISOMITTED(SubGroupLabel2), SubGroupLabel2=""), #Value!, SubGroupLabel2),
// Local Named Formulas
Titles, HSTACK(GroupHeading, GroupHeading & " Description", "", PeriodHeadings, "Totals"),
GrandTotals, BYCOL(SubGroupTotals3, LAMBDA(Col, SUM(Col))),
GrandTotal, HSTACK("", "", "Grand Total", GrandTotals, SUM(GrandTotals)),
Rows, ROWS(GroupIDColumn) * 4,
Cols, COLUMNS(Titles),
// Procedure
Detail, MAKEARRAY(
Rows,
Cols,
LAMBDA(Row, Col,
LET(Summary, MOD(Row - 1, 4) + 1,
Line, QUOTIENT(Row - 1, 4) + 1,
SWITCH(
Col,
1, IF(Summary = 1, INDEX(GroupIDColumn, Line, 1), ""),
2, IF(Summary = 1, INDEX(GroupDescColumn, Line, 1), ""),
3, INDEX(HSTACK(SubGroupLabel1, SubGroupLabel2, "Subtotal", ""), Summary),
Cols, SWITCH(
Summary,
1, SUM(GetRowλ(SubGroupTotals1, Line)),
2, SUM(GetRowλ(SubGroupTotals2, Line)),
3, SUM(GetRowλ(SubGroupTotals3, Line)),
""
),
SWITCH(
Summary,
1, INDEX(SubGroupTotals1, Line, Col - 3),
2, INDEX(SubGroupTotals2, Line, Col - 3),
3, INDEX(SubGroupTotals3, Line, Col - 3),
""
)
)
)
)
),
Result, VSTACK(Titles, Detail, GrandTotal),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
/* FUNCTION NAME: ReportGroupDetailOffsetλ
DESCRIPTION:*//**Report created for tracking budget against spend. In that context, Group is an account, Detail are budgeted line items and Offset is the sum of spending for period. The basic layout is:
Group
Detail Lines
Detail Subtotal Row
Offset Subtotal Row
Group (Detail subtotal - Offset Subtotal) Subtotal Row
Running total row*/
/* REVISIONS: Date Developer Description
Mar 04 2023 Craig Hatmaker Original Development
Mar 23 2023 Craig Hatmaker Added Grand Total
Apr 17 2023 Craig Hatmaker General structure
*/
ReportGroupDetailOffsetλ = LAMBDA(
// Parameter Declarations
[GroupIDColumn],
[GroupDescColumn],
[ItemIDColumn],
[ItemDescColumn],
[ItemValues],
[SubGroupTotals1],
[SubGroupTotals2],
[SubGroupTotals3],
[GroupHeading],
[PeriodHeadings],
[SubGroupLabel1],
[SubGroupLabel2],
// Help
LET(Help, TRIM(TEXTSPLIT(
"DESCRIPTION: →Places totals in a report format. This was designed with budgets in mind where:¶" &
"→Groups are accounts,¶" &
"→Sub group 1 is budgeted expenditures for an account,¶" &
"→Items are the detail supporing sub group 1 ¶" &
"→SubGroup 2 is actual expenditures for an account and ¶" &
"→SubGroup 3 is balances for an account.¶" &
"LAYOUT:→¶" &
"Group Heading →'Items' then Period headings¶" &
"Group ID and Description→SubGroup 1's Item descriptions then item totals by period.¶" &
"→SubGroup 2's Label then totals by period.¶" &
"→'Subtotal' then Group 3's totals by period.¶" &
"PARAMETERS:→¶" &
"GroupIDColumn →(Required) Column in group master holding unique group IDs (e.g. Account#)¶" &
"GroupDescColumn →(Required) Column in group master holding group descriptions (e.g. Account Description)¶" &
"ItemGroupColumn →(Required) Column in item detail holding group IDs (e.g. Account#)¶" &
"ItemDescColumn →(Required) Column in item detail holding item descriptions¶" &
"ItemValues →(Required) An array where each row is for one item from sub group 1 by period ¶" &
"SubGroupTotals1 →(Required) Sub group 1's (e.g. Budgeted) totals by group array where each row contains the group's totals by period ¶" &
"SubGroupTotals2 →(Required) Sub group 2's (e.g. Actuals) totals by group array where each row contains the group's totals by period ¶" &
"SubGroupTotals3 →(Required) Sub group 3's (e.g. Balances) totals by group array where each row contains the group's totals by period ¶" &
"GroupHeading →(Required) A label for group IDs (e.g. 'Account')¶" &
"PeriodHeadings →(Required) An array of period headings for each value's column¶" &
"SubGroupLabel1 →(Required) SubGroup 1's row lable (e.g. 'Budgeted')¶" &
"SubGroupLabel2 →(Required) SubGroup 2's row lable (e.g. 'Less Spent')",
"→", "¶" )
),
// Check Parameters
GroupIDColumn, IF(OR(ISOMITTED(GroupIDColumn), GroupIDColumn=""), #Value!, GroupIDColumn),
GroupDescColumn, IF(OR(ISOMITTED(GroupDescColumn), GroupDescColumn=""), #Value!, GroupDescColumn),
ItemIDColumn, IF(OR(ISOMITTED(ItemIDColumn), ItemIDColumn=""), #Value!, ItemIDColumn),
//ItemDescColumn, IF(OR(ISOMITTED(ItemDescColumn), ItemDescColumn=""), #Value!, ItemDescColumn),
ItemValues, IF(OR(ISOMITTED(ItemValues), ItemValues=""), #Value!, ItemValues),
SubGroupTotals1, IF(OR(ISOMITTED(SubGroupTotals1), SubGroupTotals1=""), #Value!, SubGroupTotals1),
SubGroupTotals2, IF(OR(ISOMITTED(SubGroupTotals2), SubGroupTotals2=""), #Value!, SubGroupTotals2),
SubGroupTotals3, IF(OR(ISOMITTED(SubGroupTotals3), SubGroupTotals3=""), #Value!, SubGroupTotals3),
GroupHeading, IF(OR(ISOMITTED(GroupHeading), GroupHeading=""), #Value!, GroupHeading),
PeriodHeadings, IF(OR(ISOMITTED(PeriodHeadings), PeriodHeadings=""), #Value!, PeriodHeadings),
SubGroupLabel1, IF(OR(ISOMITTED(SubGroupLabel1), SubGroupLabel1=""), #Value!, SubGroupLabel1),
SubGroupLabel2, IF(OR(ISOMITTED(SubGroupLabel2), SubGroupLabel2=""), #Value!, SubGroupLabel2),
// Procedure
// Group indexes and counters
GroupIDs, GroupIDColumn,
GroupDescriptions, GroupDescColumn,
GroupCount, ROWS(GroupIDs),
GroupCounter, SEQUENCE(GroupCount),
// Detail indexes and counters
Details, ItemIDColumn,
DetailCount, ROWS(Details),
DetailCounter, SEQUENCE(DetailCount),
// Report indexes and counters
ReportRows, DetailCount + GroupCount * 5,
RowCounter, SEQUENCE(ReportRows),
// Group Positioning
GroupDetailLines, COUNTIFS(Details, GroupIDs),
GroupBlockRows, GroupDetailLines + 5,
GroupBlockEnds, SCAN(, GroupBlockRows, LAMBDA(Acc, Val, Acc + Val)),
GroupBlockStarts, GroupBlockEnds - GroupDetailLines - 4,
GroupEnds, GroupBlockStarts + GroupDetailLines - 1,
GroupHeaderIndex, BYROW(
RowCounter,
LAMBDA(Row, XLOOKUP(Row, GroupBlockStarts, GroupCounter, "", 0))
),
GroupIndex, IFNA(MATCH(RowCounter, GroupBlockEnds + 1, 1) + 1, 1),
GroupRow, BYROW(
RowCounter,
LAMBDA(Row, MAX((Row >= GroupBlockStarts) * (Row <= GroupEnds) * SEQUENCE(GroupCount)))
),
// Detail line and Subtotal line positioning
DetailIndex, SCAN(0, GroupRow, LAMBDA(Acc, Val, Acc + N(Val <> 0))) * N(GroupRow <> 0),
SubtotalIndex, SCAN(0, DetailIndex, LAMBDA(Acc, Val, IF(Val = 0, Acc + 1, 0))),
// Report Descriptive Values
GroupID, IF( GroupHeaderIndex = "", "", INDEX(GroupIDs, GroupHeaderIndex, 1)),
GroupDescription, IF( GroupHeaderIndex = "",
"",
INDEX(GroupDescColumn, GroupHeaderIndex, 1)
),
DetailDescription, IF( DetailIndex <> 0,
INDEX(ItemDescColumn, DetailIndex),
SWITCH(
SubtotalIndex,
1, SubGroupLabel1 & " Subtotal",
2, SubGroupLabel2,
3, GroupHeading & " Subtotal",
4, "Cumulative Total",
""
)
),
// Report values
ReportColumns, COLUMNS(PeriodHeadings),
ColumnCounter, SEQUENCE(, ReportColumns),
Detail, INDEX(ItemValues, DetailIndex, ColumnCounter),
DetailSubtotal, INDEX(SubGroupTotals1, GroupIndex, ColumnCounter),
OffsetSubtotal, INDEX(SubGroupTotals2, GroupIndex, ColumnCounter),
GroupSubtotal, DetailSubtotal + OffsetSubtotal,
RunningTotal, RunningTotalInRowsλ(GroupSubtotal),
LineValues, IF( DetailIndex <> 0,
Detail,
SWITCH(
SubTotalIndex,
1, DetailSubtotal,
2, OffsetSubtotal,
3, GroupSubtotal,
4, RunningTotal,
""
)
),
// Report Sections
Header, HSTACK(GroupHeading, GroupHeading & " Description", SubGroupLabel1, PeriodHeadings),
Body, HSTACK(GroupID, GroupDescription, DetailDescription, LineValues),
Grand, HSTACK("", "", "Grand Total", ByCol(SubGroupTotals3, LAMBDA(Col, Sum(Col)))),
GrandRT, HSTACK("", "", "Cumulative Total", RunningTotalInRowsλ(ByCol(SubGroupTotals3, LAMBDA(Col, Sum(Col))))),
Result, VSTACK(Header, Body, Grand, GrandRT),
// Handle Error
Error, OR( ISERROR( Result)) + 1,
// Return Result
Choose(Error, Result, Help)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment