EDIT 1 → 2024-03-30:

• Included parameters in the lambda functions so the table names and columns may be injected by the user
• Updated lambda functions with such paramteres
• Even though I found a solution, I tested the whole programming and it's really resource/memmory intensive.

There must be a way to optimize/simplify it

---

---

Objective:

Calculate products' costs for a range of dates based on a Bill of Materials and purchase price history of each material (component)

---

Process:

Get products' components and quantities up to the purchase level and then multiply them by their purchase price in each month.

Source data

TableBOM has the mix of components needed in each product (Q = qty)

TablePurchaseComponent has the historical prices of each material (component)

Tables in the sheet:

---

Lambda functions (as defined names):

fxProcessVal:

Parameters → lookup_val;component1_cols;component2_cols;result_col

Code:

=LET(
    comp_res; VSTACK(
        FILTER(component1_cols; result_col = lookup_val);
        FILTER(component2_cols; result_col = lookup_val)
    );
    comp_res_fil; FILTER(comp_res; CHOOSECOLS(comp_res; 2) <> "");
    lookup_col; IFNA(EXPAND(lookup_val; ROWS(comp_res_fil)); lookup_val);
    IFERROR(HSTACK(lookup_col; comp_res_fil); "")
)

fxProcessCompRow:

Parameters → data;lookup_row;component1_cols;component2_cols;result_col

Code:

=LET(
    sourceData; fxProcessVal(
        INDEX(data; lookup_row; 2);
        component1_cols;
        component2_cols;
        result_col
    );
    res_val; INDEX(data; 1; 1);
    res_col; IFNA(EXPAND(res_val; ROWS(sourceData)); res_val);
    sourceRes; HSTACK(res_col; DROP(sourceData; 0; 1));
    IF(
        sourceData <> "";
        FILTER(sourceRes; CHOOSECOLS(sourceRes; 2) <> "");
        CHOOSEROWS(data; lookup_row)
    )
)

fxProcessComp:

Parameters → source;component1_cols;component2_cols;result_col

Code:

=LET(
    seq; SEQUENCE(ROWS(source));
    reducer; REDUCE(
        "";
        seq;
        LAMBDA(acc; curr;
            VSTACK(
                acc;
                IFNA(
                    fxProcessCompRow(source; curr; component1_cols; component2_cols; result_col);
                    HSTACK(""; ""; "")
                )
            )
        )
    );
    temp_res; DROP(reducer; 1);
    temp_res
)

fxCompCost:

Parameters → component1_cols;component2_cols;result_col

Code:

=IFNA(
    INDEX(
        unitprice_col;
        MATCH(
            MAXIFS(purchasedate_col; purchasedate_col; "<=" & date; purchasecomponent_col; comp) &
                comp;
            purchasedate_col & purchasecomponent_col;
            0
        )
    );
    0
)

prox:

Parameters → seed;component1_cols;component2_cols;result_col

Code:

=LET(
    res; IF(
        COUNTA(seed) = 1;
        fxProcessVal(seed; component1_cols; component2_cols; result_col);
        fxProcessComp(seed; component1_cols; component2_cols; result_col)
    );
    comp; CONCAT(seed) = CONCAT(res);
    IF(comp; seed; prox(res; component1_cols; component2_cols; result_col))
)

---

Reference:

Formulas

Formula to explode the BOM list (and desaggregate each product material and quantity needed):

=UNIQUE(DROP(REDUCE("";TableBOM[Resultado];LAMBDA(acc;curr;VSTACK(acc;prox(curr;TableBOM[[Componente 1]:[Q1]];TableBOM[[Componente 2]:[Q2]];TableBOM[Resultado]))));1))

The dates to calculate the costs of each product are dynamically set in a range using this formula:

=DATE(YEAR(Q1);SEQUENCE(1;DATEDIF(Q1;Q2;"M")+1;MONTH(Q1);1);1)

STUCKED HERE:

I would like to set Q3 (dates range as a spilled range) and calculate the cost for each product in each month.

Currently the SUMPRODUCT calculates the total for all the months (dates in the range).

I've tried BYCOL and a combination of MAP and Sequence but haven't figure it out.

Any help I would appreciate it.

Link to read-only sample file (no macros)

=LET(
     data;L4#;
     date;Q3#;
     res;INDEX(data;;1);
     comp;INDEX(data;;2);
     q;INDEX(data;;3);
     lab;UNIQUE(res);
     cost;q*fxCompCost(comp;date);
     rt;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*cost)));
     temp;HSTACK(lab;rt);
temp
)

Desired result:

I used a combination of REDUCE and SEQUENCE to iterate through each date column.

=LET(
     data;L4#;
     dateRow;DATE(YEAR(ParamStartDate);SEQUENCE(1;DATEDIF(ParamStartDate;ParamEndDate;"M")+1;MONTH(ParamStartDate);1);1);
     headers;HSTACK("";dateRow);
     date;dateRow;
     res;INDEX(data;;1);
     comp;INDEX(data;;2);
     q;INDEX(data;;3);
     lab;UNIQUE(res);
     cost;q*fxCompCost(comp;date);
     reducer;REDUCE("";SEQUENCE(COLUMNS(date));LAMBDA(acc;curr;HSTACK(acc;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*CHOOSECOLS(cost;curr)))))));
     result;VSTACK(headers;HSTACK(lab;DROP(reducer;;1)));
     result
)

Names added to the worksheet:

ParamStartDate: Q2 ParamEndDate: Q3

Final:

If someone comes up with a better solution even to process the BOM please post it.

Hope this helps somebody else.