Create all possible combinations of two lists of values in Excel
Learn how we can create all possible combinations of values in two lists - using formulas in Excel. Ask Your Excel Question #16
For an excel template that generates combinations of 6 lists, please visit https://indzara.com/2019/11/combinati...
Video Demo: • Combinations Generator - Free Excel T...
For the latest templates on Combinations Generator, Please visit
Combinations Generator (10 Lists): • Combinations Generator Excel Template...
Combinations Generator (1 list - No repetitions - N = 20 , R = 7; • Combinations Generator Excel Template...
Product Page for Download: https://indzara.com/product/combinati...
Best wishes.
Learn Excel at https://indzara.com/courses
Playlist: • How to Create Drop Down List in Excel...
Welcome to this week's episode of Ask your Excel Question. In this week we are going to learn how we can create all possible combinations of values from two lists. For example, if you have a list of locations and departments and you want to calculate or create a list of all combinations of location and department - each location will have different departments, and you'll have New York, HR New York Finance. Similarly, Sydney will have Sydney, HR Sydney finance. So you want to create all possible combinations of values from two such lists, we can do those using the formulas.
1. Convert two lists to tables.
2. Calculate counts of values in each of the 2 tables.
3. Calculate the row number in Location table by QUOTIENT, MOD and ROW functions.
4. Calculate the row number in Department table by COUNTIF function.
5. Use the INDEX function to pull the location and the department names
6. Calculate the combination by concatenating the two names
If you found this tip useful, please post your comments. Also, share this with your friends. I look forward to seeing you in the next week's episode of Ask your Excel Question.
Functions Used: MOD, QUOTIENT, INDEX, ROW, COUNTIF, COUNTA
Thank you very much for watching.
Social:
Subscribe to YouTube: http://www.youtube.com/user/theindzar...
Facebook: / theindzara
