Forum Discussion
Provide me with all main products (which are not present in the sub products) with their lowest
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 ?srivatsa224 Possibly, but you would need to be able to somehow identify which records go together. If the dataset doesn't contain an ID column (ie: Group_ID) that can be used to link the related records together, and the data is not in any kind of logical order, it would be pretty tough to make this happen.
Can it always be determined that when a "non-Ground" record appears, the next occurrence of that particular Sub-product, found in the Product column, is the related record? For example, when "A / B / Sub1" appears, does that mean the next "B / ? / Sub2" or "B / ? / Ground" record is always the related record?
Don't get me wrong but I've always been intrigued by you guys' love of =Let in office365. perhaps it is because I don't use Excel that often.
Today I finally find a good use case of Let function so I decided to write another version of recursive lambda:
