Forum Discussion
Provide me with all main products (which are not present in the sub products) with their lowest
The 'Ground' level can be implicitly determined within your data structure as the termination point of a recursive function.
Take into account the following columns for input: products, subs, and factors:
=LET(
rows_sequence, SEQUENCE(ROWS(products)),
ground_subs, MAP(rows_sequence, LAMBDA(i, ground_sub(i, products, subs))),
total_factors, MAP(rows_sequence, LAMBDA(i, total_factor(i, products, subs, factors))),
data, HSTACK(products, ground_subs, total_factors),
FILTER(data, NOT(ISNUMBER(XMATCH(products, subs)))))
Recursive function ground_sub, which identifies the 'original' subproduct of any given product:
=LAMBDA(i, products, subs, LET(
sub, INDEX(subs, i),
new_i, XMATCH(sub, products),
IF(ISNUMBER(new_i), ground_sub(new_i, products, subs), sub)))
Recursive function total_factor, designed to multiply all intermediary factors of any given product:
=LAMBDA(i, products, subproducts, factors, LET(
subproduct, INDEX(subproducts, i),
new_i, XMATCH(subproduct, products),
INDEX(factors, i) * IF(ISNUMBER(new_i), total_factor(new_i, products, subproducts, factors), 1)))Hi,
Instead of writing two recursive lambdas and use map to get results in one go, I think you can just write a recursive lambda with row Index as parameter, and put SEQUENCE inside the lambda.
Writing lambda with row Index as parameter is very similar to the classic recursive factorial function.
I checked, I think it works pretty well:
Thank you, rachel.,
This is a better approach, and I've downloaded your worksheet. However, I'm unable to refresh the formula in cell B14. I've tried pressing ENTER and CTRL + SHIFT + ENTER within the range B14:D22. How did you apply the formula?
