Forum Discussion
Provide me with all main products (which are not present in the sub products) with their lowest
I hope you are doing well, please find the problem statement below along with the sample data and output.
I have data with a product, sub-product, level and multiplying factor. Each product has multiple sub products, these sub products again have further subproducts, forming a hierarchy of data. Level "Ground" defines the lowest level of data. Below is the sample data.
| Product | Sub product | Level | Multiplying Factor |
| A | B | Sub1 | 0.9 |
| A | C | Ground | 0.8 |
| A | D | Ground | 0.7 |
| B | F | Ground | 0.3 |
| G | H | Sub1 | 0.87 |
| H | I | Sub2 | 0.99 |
| I | J | Ground | 0.88 |
| G | B | Sub1 | 0.89 |
| B | F | Ground | 0.3 |
I need an output which will provide me with all main products (which are not present in the sub products) with their lowest level sub-products and denoted by Level " Ground" and their multiplying factor multiplied with each level until reaching "Ground" level. Below is a sample output of data. How do I obtain this in Excel.
| Product | Sub product | Level | Multiplying Factor |
| A | C | Ground | 0.8 |
| A | D | Ground | 0.7 |
| A | F | Ground | 0.9*0.3 |
| G | J | Ground | 0.87*0.99*0.88 |
| G | F | Ground | 0.89*0.3 |
11 Replies
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?
Product Sub_product Level Multiplying_Factor
A B Sub1 0.9 A C Ground 0.8 A D Ground 0.7 B F Ground 0.3 G H Sub1 0.87 H I Sub2 0.99 I J Ground 0.88 G B Sub1 0.89 B F Ground 0.3 Product Sub_product level Multiplying_Factor
A C Ground 0.8 A D Ground 0.7 A F Ground 0.27 G F Ground 0.267 G J Ground 0.757944 SQL:
select * from Bom_Calc_Factor; with recursive under_alice as (select Product top,level,Sub_product,Product,Multiplying_Factor,Multiplying_Factor path from Bom_Calc_Factor where Product in (select distinct Product from Bom_Calc_Factor where Level like 'Sub1') union select top,x.level,x.Sub_product,x.Product,x.Multiplying_Factor,under_alice.path*x.Multiplying_Factor from Bom_Calc_Factor x join under_alice on x.Product=under_alice.Sub_product) select top Product,Sub_product,Level,path Multiplying_Factor from under_alice where Level like 'GROUND';srivatsa224 Based on the sample data provided, another option that might work is to use the REDUCE / VSTACK method to iterate through the main product data, applying a recursive LAMBDA function to each record:
=LET( arr, UNIQUE(A2:D10), main, FILTER(arr, ISNA(XMATCH(CHOOSECOLS(arr, 1), UNIQUE(CHOOSECOLS(arr, 2))))), data, DROP(main,, 1), results, DROP(REDUCE("", SEQUENCE(ROWS(data)), LAMBDA(v,n, VSTACK(v, NEXTLEVEL(arr, CHOOSEROWS(data, n))))), 1), HSTACK(CHOOSECOLS(main, 1), results) )The recursive LAMBDA function, NEXTLEVEL, is defined in Name Manager as follows:
NEXTLEVEL: =LAMBDA(array,record, IF(INDEX(record, 2)="Ground", record, NEXTLEVEL(array, LET( sub, INDEX(record, 1), arr, FILTER(DROP(array,, 1), CHOOSECOLS(array, 1)=sub, HSTACK(sub, "Ground", 1)), IF(ISNUMBER(arr), record*arr, arr) ) ) ) )Results
Please see the attached file...
srivatsa224 Further to my previous response, if the sample data provided is not a complete and accurate representation of your actual data, there are certain scenarios that can return errors. For example, if the sub product combo "B / F" does not always appear at the "Ground" level, or if "B" can also be combined with other sub products such as "B / K".
Another option would be to use the SCAN function to assign ID numbers to each product grouping; however, this method requires the data to be arranged so that each product series appear together and in order from Sub1, Sub2, etc. to Ground. For example, your sample data shows the first record "A / B / Sub1" is separated from its corresponding "B / F / Ground" record by two other records. "A / B / Sub1" would need to be followed by "B / F / Ground" with nothing else in between them. If this is always true for your actual data, then the following formula should work:
=LET( data, A2:D16, id, SCAN(0, SEQUENCE(ROWS(data)), LAMBDA(v,n, IFS(n=1, 1, INDEX(data, n-1, 3)="Ground", v+1, TRUE, v))), DROP(REDUCE("", UNIQUE(id), LAMBDA(v,n, LET( arr, FILTER(data, id=n), VSTACK(v, HSTACK(TAKE(arr, 1, 1), CHOOSECOLS(TAKE(arr, -1), 2, 3), PRODUCT(CHOOSECOLS(arr, 4))))))), 1) )Results
Please see the attached file...
- Thanks for the solution, I tried for the given data and it works fine, but the complete data is large and
the sub product combination is not always the same and might vary. Also the product series data is not arranged together. It's not working in this case. Would you happen to have any solution ?
