Quickly Get All Combinations of Multiple Lists in Excel

[@rincyr]

Awesome! this video helped me manage my work without depending on others. Thank you :)

[@Up4Excel]

Great to hear that. It feels good to be independent didn't it 👍 John

[@khalidmajeed2886]

mashallah sir,,,those divide their knowledge into others will receive where they do not know

[@Up4Excel]

Thank you for commenting. Hope you found the video useful 👍

[@DaveIsAtWork-Really]

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

[@Up4Excel]

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 😉

[@BiblicalBaseball]

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.)

[@Up4Excel]

Not sure how you'd do that but this technique is about combining data rather than creating the combinations.

[@ndeleonn]

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.

[@Up4Excel]

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 👍

[@AMN200768]

When I get to the point of saving the combined list, its not letting me "close and load to". I only get the option to "close and load". What am I doing wrong?

[@Up4Excel]

You only get close and load to the first time for the first query. After that it defaults to the first option you chose. However, if you click on the queries and connections button on the data ribbon in main Excel you can see all the queries, and then right click on each one in turn and select 'close and load to' to change the settings. In short, no, you didn't do anything wrong 🙂

[@richardhay645]

VSTACK, HSTACK with EXPAND?

[@Up4Excel]

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?

[@richardhay645]

@@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!!