Get all possible combinations from multiple lists in Excel using Power Query | Excel Off The Grid

[@karananand1123]

This technique helped to reduce our work by a week and got appreciation at my workplace!! Thanks a lot !!

[@ExcelOffTheGrid]

Great to hear! 😀

[@nikkyb]

Such a help. I've never ventured into Power Query and never would have looked there for a solution if it hadn't been for the title of this video. Very easy to follow and the video lived up to the title exactly. Thank you!

[@syedammarhasan9611]

This is a life-saver. I used method # 2. Thank you.

[@muhammadobaidkhan4099]

one of the best way of teaching excel I watched today

[@ExcelOffTheGrid]

Thanks, that's very kind of you to say. 😁

[@dzn8575]

As a business analyst can't explain how imp this is ... Earlier I use to spend lot of time to manually create the scenarios but with this it's a job of minutes. Thanks a lot!

[@ExcelOffTheGrid]

You’re welcome.

[@Freakysantana]

using this to take two different sets of 4 columns to make an overall score matrix, thank you this is very helpful!

[@ExcelOffTheGrid]

💥 BOOM! Great stuff.

[@alexeytheinterpreter]

Thank you for this detailed tutorial, I tried Solution #1 and it worked at the very first attempt.

[@ExcelOffTheGrid]

Yay!! Good work.

[@ym10up]

This is excellent!!! I should have finished watching the entire video before trying it with my data though. The second solution is so much easier! Thank you for explaining it so clearly. I'm going to subscribe.

[@ExcelOffTheGrid]

Thanks Rebecca. The first method refreshes faster if you have a lot of data, but the second method is easier to implement.

[@ym10up]

@@ExcelOffTheGrid good to know the difference. Thank you!

[@DaveIsAtWork-Really]

This is exactly what I have been looking for! Very easy to follow. I think I'll stick with Option 1 for now, until I get a better grasp on working with the data model and power query. Old dog learning new tricks and all :)

[@mss2872]

Excellent! This is what I'm looking for the whole day. Thanks for make it searchable!

[@ExcelOffTheGrid]

Glad to help! :-)

[@fennecfennec5234]

Excellent. you helped me a lot . Million Thanks.

[@ExcelOffTheGrid]

You're welcome! 😀

[@michaelkomisarenko6426]

Thanks for sharing. I am in the process of creating a tree structure and have found this video very helpful.

[@ExcelOffTheGrid]

Great news, glad I could help.

[@skeeterbodeen8326]

I’ve done this in Access but couldn’t figure it out in PQ. It took me a week to find your solution, so I am incredibly grateful. Btw. Using it to create a unique Company-Location-Dept-Acct# key for Mthly power pivot reporting. 👍🏻🙏🏻😎

[@ExcelOffTheGrid]

Great work - glad it was helpful.

[@nsanch0181]

Thank you for the power query example, it was very helpful. Looking farward to seeing what else you have.

[@ExcelOffTheGrid]

Thanks 😀

[@Will_Moffett]

Great new Excel channel. I hope you have success here as this channel seems to read my mind as to what I am looking for in solutions to problems that actually come up in work.

[@ExcelOffTheGrid]

Thanks Will 😀

[@finnovaticsbusinesssolutions]

This is of great help to our work, many thanks!

[@meucanaltest1o23o24]

Hello Mark! Just used it to create a table based on 9 different ones with many lines, worked flawless with solution 2. Thank you so much :)

[@ExcelOffTheGrid]

That's great news. Good work :-)

[@LizSim-r9w]

really well explained, thank you! exactly what we needed

[@ExcelOffTheGrid]

Great news 😁

[@coreysmith6829]

Amazing, I was able to do 9 columns, 7200 combinations total in just a few minutes.

[@ExcelOffTheGrid]

Good work!!! 9 columns is a crazy number to do this for.

[@paulsinclair7560]

Thanks Mark another great example explained very well

[@ExcelOffTheGrid]

Thanks Paul 😀

[@Up4Excel]

Thanks Mark for solving a very common requirement in Excel. I especially love the second solution that was completely new to me and a great shortcut 👍

[@ExcelOffTheGrid]

Thanks, I appreciate the feedback. It’s always nice to share a technique somebody didn’t know before.

[@markjreichert]

This worked great! Thanks for the tips

[@viviennesmithdorf3244]

Excellent - thank you!

[@dominicrozario8311]

Thank you so Much! You just Saved my Day! ❤😭

[@ExcelOffTheGrid]

Woop woop! I'm glad I could help 😁

[@BIGorilla]

Great example Mark, helpful in generating all combinations! 🔥🙌

[@ExcelOffTheGrid]

Thanks Rick. 👍

[@rohithcunni4797]

Thanks a lot for uploading. Very very useful.👍✌

[@ExcelOffTheGrid]

You’re welcome 👍

[@bluex5115]

good job.. thanks for sharing us this ... and others PQ example .. over 15y working with vba and because of inconsistency of data i didnt use PQ... but i want to learn and to use in the future .. hope to see more demo PQ here

[@ExcelOffTheGrid]

Power Query is an amazing tool. If data wrangling is one of your main tasks, it is definitely what you need. I have got a blog series that you might find useful for getting started: exceloffthegrid.com/power-query-introduction/

[@Loewenbrandt]

Thank you, it was very helpfull. 👍

[@ExcelOffTheGrid]

Great news 👍

[@lenadod]

This si an amazing video, helped me a lot

[@BenjaRuLo]

Really nice! Thank you so much!

[@ExcelOffTheGrid]

I’m glad I could help 😀

[@nagendrasa9774]

good one, thanks for sharing

[@ExcelOffTheGrid]

Thanks for watching 😁

[@kurkgang275]

excellent.... thank you very much

[@iamdeepshekhar]

Great Content. Thank you :)

[@vigilance3109]

Thank you so much!

[@ExcelOffTheGrid]

You're welcome!

[@VincentCharlety-e4i]

Thank you very much for this tutorial, very clear. I am new to Power Query, is it possible to produce the same results with the columns of a unique table ? To apply this I was forced to split my table into 3 separate ones I thought it was a shame I had to do this.

[@ExcelOffTheGrid]

Yes, you can join the Table back with itself. Then after you’ve joined it, delete the columns you don’t need.

[@vishnus679]

Very much useful

[@ExcelOffTheGrid]

You’re welcome.

[@GeertDelmulle]

Although it is possible to do this in Excel with dynamic arrays, your second solution is the best one and has me convinced. Too bad you didn’t emphasise the fact that the “custom column” feature actually iterates (“each”) over the table you start from, effectively yielding the exact same result as the Outer Join in the first solution. IMO your second solution is the best overall.

[@ExcelOffTheGrid]

Thanks Gerrt. You’re right, I did miss that part. Hopefully based on it being a ‘table’ most people will realise that it provides the table for each row. 😀

[@aldotanca9430]

Excellent

[@luismunoz90.]

Ive never used excel before so i'm new at this. I'm trying to make a chart of all the different combinations of 10 games, picking if each game goes Over/Under in points. So, saying: O,O,O,O,O,O,O,O,O,O is one combination, then O,U,U,U,U,U,U,U,U,U is another combination. For 10 games, there's 1024 different combinations. to find this number, the formula is 2^10, 2 being the number of scenarios, Over or Under, and 10 number of games.

[@alexkakana9628]

Ho To Do It Can you Assist Please

[@jordanbuffaloe]

Thank you!

[@ExcelOffTheGrid]

You’re welcome.

[@Drusopp]

Question, How do you set conditions to eliminate some unwanted combinations?

[@Sabrina-lg7ji]

thank you so much!

[@ExcelOffTheGrid]

You're welcome!

[@sharmilethaneshkumar531]

Thanks Mark

[@ExcelOffTheGrid]

You’re welcome.

[@hazemali382]

More than great more than Simple

[@ExcelOffTheGrid]

Thanks. 😁

[@ramalingamravichandran-hx7jn]

Bro, good evening, how to generate/ bring the values of all possible formates/ complete possibilities. Eg. Question from A To Z going to pick for letter 4 letter combinations like ABCD, ACDE, ADEF, etc., please understand I am not looking for this numerical answer 14,950. Please help

[@hawkyt5650]

Can we also use it for names

[@ExcelOffTheGrid]

Yes, you can use it for names.

[@rocvox]

My version of Excel differs in some ways and this has not been working. I had no join kind in merge tables. I was able to follow to a certain point but some of the options you showed were not available for my version. I don't know Excel well enough to get this done.

[@ExcelOffTheGrid]

How old is your version of Excel? Those options have been there for a long time.

[@rocvox]

It's new. I actually found my answer - thanks!@@ExcelOffTheGrid

[@vhc6600]

If you had a list of numbers how would you get all the possible sum values if you summed all the possible combinations and displayed which ones get you which answer, very simple example... 1+2, 1+3, 1+4, 1+2+3, 1+2+3+4, 2+3, 2+4, 2+3+4, 3+4

[@ExcelOffTheGrid]

I think you could apply the same approach. Then add a calculated column to sum the columns together into a single value. Finally, if you are only interested in the unique values you would need to remove duplicates.

[@planxlsm]

08:49 Blank Query

[@tejpatnala709]

Will this process work with objects having different elements for example: 5 objects A,B,C,D,E A having 5 , B having 2 , C having 3 , D having 2 , E having 4 elements????

[@ExcelOffTheGrid]

Yes, it will.

[@nithyashreeraja5241]

isthere any way to delete repeating combos?

[@ExcelOffTheGrid]

Just use the remove duplicates transformation in the original lists, or the final query.

[@nithyashreeraja5241]

@@ExcelOffTheGrid thankyouuuu

[@amithenry9841]

Lovely

[@ExcelOffTheGrid]

Thanks 😀

[@TheBairdNecessity]

what if i have a multiple lists of words and in those lists they have the same words, i dont want my combinations to have the same word more than once how would i go about making this possible.

[@ExcelOffTheGrid]

Use the remove duplicates option before combining the data, you can find it in Home > Remove Rows > Remove Duplicates

[@frankb1]

Thanks

[@ExcelOffTheGrid]

No problem

[@ashash6537]

Help i need a formula from you guys that pics all combinations of 6 races where there are 2 horses in each race

[@ashash6537]

so races 1 to 6 horse A and A in first race and B B2 in race 2 and C1 C2 etc give me all the combinations of this or how to do it

[@depth386]

What I am after is, once I have this list.. I want to mark some of them as unavailable or unacceptable ("you can get the bicycle in pink or red, but not blue") and furthermore I want to see if I can get a unique color for every product.

[@khalidmajeed2886]

what will do if rows r in 1000

[@ExcelOffTheGrid]

Sorry, I’m not sure I understand and the question. Can you give some more details.

[@moniomar12]

Cool

[@sumeetkavathekar3694]

Unable to find from Table/Range from Data

[@ExcelOffTheGrid]

Which version of Excel are you using?

[@nkowk]

👍👍👍

[@dougmphilly]

i knew example 1 but i'll be damned on how simple example 2 is

[@ExcelOffTheGrid]

Thanks - sometimes it is the simplest things which we don't even consider :-)

[@anhnhatnguyet4628]

I have data Col A Col B Col C Col D 1 A B C 2 X Y Z 3 5 6 7 I want convert to 1 A X 5 1 B Y 6 1 C Z 7 2 A X 5 2 B Y 6 2 C Z 7 3 A X 5 3 B Y 6 3 C Z 7 >> Use formula or VBA or another method, how ?

[@anilkumarsharma8901]

this command will save the hotel industry and found the loss and profit for next millions of years as future is always predictable 😂😂😂😂😂😂😂😂🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣

[@joseadamadorna147]

Thank you!

[@ExcelOffTheGrid]

You’re welcome.