Very nice, it gave me the PQ info that I was looking for to put all the combinations into one result table. This approach needs Refresh, which is not a problem where I expect to use it, but do you have any idea how to build a MAKEARRAY to get a dynamic list from one formula? I've gotten it this far, but can't get the LAMBDA part: =Let(T1R,ROWS(tblProdCat),T2R,ROWS(tblRegion),T3R,ROWS(tblCustSeg),MAKEARRAY(T1R*T2R*T3R,3,LAMBDA( ........ I don't work with LAMBDA enough to get a real feel for it, but once the 544x3 array is made I've got a feeling it should be able to handle filling it out. David
Glad you got what you needed for the query. Like you though, I have very little experience with LAMBDA so not going to be able to help. I tend to avoid anything other people using my spreadsheets can't fix themselves 😉
This is interesting. However, can PQ be used to get unique combinations? For example, if I have three lists of four objects (A,B,C,D) can PQ be used to generate AAA, AAB etc, but not duplicates like ABA or BAA? This would be advantageous because the number of unique combinations is much less than the number of combinations. In this case, the number of combinations is 4^3 = 64 and the number of unique combinations is 20.
Not sure if this is the best method, but one approach in PQ could be this: run the same process as before but also give each item a unique value. Then sum each row and remove the duplicate rows, then remove all the value columns to clean it up. John 👍
@@Up4Excel I've used the functions but haven't tried it with your exact setup but I think they can do it. Actuaĺy pretty straightforward application of these functions unless there is some hidden quirk in your setup that I missed. Imteresting video but I watched it quickly p n my phone while fixing breakfast! That's why the question mark at the end of my comment!!
Any chance we're able to do this with parameters? Like each combination must equal 100%? For instance, five pieces of a pie divvied up into 5% increments? (i.e. combination 1 - 100%, 0%, 0%, 0%, 0%; combination 2 - 95%, 5%, 0%, 0%, 0%; combination 3 - 95%, 0%, 5%, 0%, 0%; etc.)
Awesome! this video helped me manage my work without depending on others. Thank you :)
Great to hear that. It feels good to be independent didn't it 👍 John
mashallah sir,,,those divide their knowledge into others will receive where they do not know
Thank you for commenting. Hope you found the video useful 👍
Very nice, it gave me the PQ info that I was looking for to put all the combinations into one result table. This approach needs Refresh, which is not a problem where I expect to use it, but do you have any idea how to build a MAKEARRAY to get a dynamic list from one formula? I've gotten it this far, but can't get the LAMBDA part:
=Let(T1R,ROWS(tblProdCat),T2R,ROWS(tblRegion),T3R,ROWS(tblCustSeg),MAKEARRAY(T1R*T2R*T3R,3,LAMBDA( ........
I don't work with LAMBDA enough to get a real feel for it, but once the 544x3 array is made I've got a feeling it should be able to handle filling it out. David
Glad you got what you needed for the query. Like you though, I have very little experience with LAMBDA so not going to be able to help. I tend to avoid anything other people using my spreadsheets can't fix themselves 😉
This is interesting. However, can PQ be used to get unique combinations? For example, if I have three lists of four objects (A,B,C,D) can PQ be used to generate AAA, AAB etc, but not duplicates like ABA or BAA? This would be advantageous because the number of unique combinations is much less than the number of combinations. In this case, the number of combinations is 4^3 = 64 and the number of unique combinations is 20.
Not sure if this is the best method, but one approach in PQ could be this: run the same process as before but also give each item a unique value. Then sum each row and remove the duplicate rows, then remove all the value columns to clean it up. John 👍
VSTACK, HSTACK with EXPAND?
Seems like it might work... But I'd have to have a play around to see how to do it. Do you have the answer Richard?
@@Up4Excel I've used the functions but haven't tried it with your exact setup but I think they can do it. Actuaĺy pretty straightforward application of these functions unless there is some hidden quirk in your setup that I missed.
Imteresting video but I watched it quickly p n my phone while fixing breakfast! That's why the question mark at the end of my comment!!
Any chance we're able to do this with parameters? Like each combination must equal 100%? For instance, five pieces of a pie divvied up into 5% increments? (i.e. combination 1 - 100%, 0%, 0%, 0%, 0%; combination 2 - 95%, 5%, 0%, 0%, 0%; combination 3 - 95%, 0%, 5%, 0%, 0%; etc.)
Not sure how you'd do that but this technique is about combining data rather than creating the combinations.