ExcelRobot

/*
Name:  Lag By N (Lag)
Description:  Return the array shifted back by the specified number of positions.
Parameters:
   array - original array
   [lag_by] - number of positions to lag by (default: 1)
   [pad_with] - value to fill empty position with (default: #N/A)
   [scan_by_column] - TRUE - scans by columns, FALSE - scans by rows (default)
Source:  Excel Robot (@ExcelRobot)
*/

ExcelRobot

/*
Name:  Sort Street Addresses (StreetSort)
Description:  Sorts a list of street addresses so that the street names are together with the address number in the correct order.  Also supports proper sorting of street names like 1st Street, 2nd Street, 10th Street into numerical order.
Parameters:
   addresses - array of street addresses (ie: 123 Main Street)
Source:  Excel Robot (@ExcelRobot)
*/
StreetSort =LAMBDA(addresses, LET(
   \\LambdaName, "StreetSort",
   \\CommandName, "Sort Street Addresses",

ExcelRobot

/*
Name:  RegEx Expression From Generative AI (RegExFromAI)
Description:  Given a natural language input, uses Excel Labs' Generative AI function to return a RegEx expression.  Optionally uses Excel's new REGEXEXTRACT function to check whether the RegEx generated works on a provided example and expected value.
Parameters:
   input - natural language prompt like: extract email address
   example - optionally provide some example data to help train the AI
   expected - optionally provide the expected value to be returned given the example data
Source:  Excel Robot (@ExcelRobot)
*/
RegExFromAI =LAMBDA(input, [example], [expected], LET(

ExcelRobot

'--------------------------------------------< OA Robot >--------------------------------------------
' Command Name:           Backup And Save Active Workbook
' Description:            Saves the active workbook, but also makes a backup in the Archive folder
' Macro Expression:       modWorkbook.BackupAndSaveActiveWorkbook()
' Author:                 Excel Robot (@ExcelRobot)
' Generated:              08/05/2022 02:32 PM
'----------------------------------------------------------------------------------------------------
Sub BackupAndSaveActiveWorkbook()
    Dim archivePath As String
    Dim wbName As String

ExcelRobot

/*
Name:  No Examples (NoExamples)
Description:  Given the used range of a MEWC (Microsoft Excel World Championship) Case sheet, returns just the game numbers, levels, and game data excluding any example data.
Parameters:
   used_range - Reference to all used cells on Case sheet.
Source:  Excel Robot (@ExcelRobot)
*/
NoExamples =LAMBDA(used_range, LET(
   \\LambdaName, "NoExamples",
   \\CommandName, "No Examples",

ExcelRobot

/*
Name:  Convert RGB to Color Name (RGBtoColorName)
Description:  Converts RGB integers to nearest color name in a table of 139 colors.
Parameters:
   r - Red RGB Integer
   g - Green RGB Integer
   b - Blue RGB Integer
Source:  Written By Excel Robot (@ExcelRobot), converted from https://gist.github.com/XiaoxiaoLi/8031146
*/
RGBtoColorName =LAMBDA(r, g, b, LET(

ExcelRobot

/*
Name:  Two Way Table Lookup (TLOOKUP)
Description:  Looks up values in a table by values in first column and first row.  Accepts arrays of column/row values or if omitted will return all columns/rows.
Parameters:
   table - range or array that includes headers in first row and column
   [row_values] - value or array of values to lookup in first column; returns all rows if omitted
   [column_values] - value or array of values to lookup in first row; returns all columns if omitted
Source:  Excel Robot (@ExcelRobot)
*/
TLOOKUP =LAMBDA(table, [row_values], [column_values], LET(

ExcelRobot

/*
Name:  Format Formula (FormatFormula)
Description:  Uses OA Robot formula formatting api to format a formula to make it easier to read.
Parameters:
   CellOrFormula - Reference to cell containing a formula or a formula as text
   Compact - True to remove all whitespace (default: False)
Source:  @ExcelRobot, https://www.oarobot.com
*/
FormatFormula = LAMBDA(CellOrFormula, [Compact], LET(
   \\LambdaName, "FormatFormula",

ExcelRobot

=LAMBDA(percent,[color],LET(
    baseurl,"",
    IMAGE(baseurl&IF(ISOMITTED(color),"Black",color)&TEXT(percent,"0")&".png")
))

ExcelRobot

/*
Name:  Multi-Level Dropdown Validation (MultiLevelDropdownValidation)
Description:  Returns the list of allowable values for a specific cell based on 
   the entries to the left.
Parameters:
   tablename - name of the Excel table containing the data validation dropdowns.
   header - table header cells from the first column with data validation to the column for which
      to return list of values allowed.
   keylisttable - reference to dynamic range containing the MultiLevelKeyLists lambda key lists table.
Source:  Written by Excel Robot, inspired by Wyn Hopkins (https://www.youtube.com/watch?v=r0DTFodQYJQ&t=989s)