https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/
Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.
Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.
Make Cartesian product of two tables in Excel
Note: You need Excel 2013 or above for this.- Convert two lists to tables, if not already done.
- Select any cell in one of the tables and go to Insert > Pivot Table (Use ALT + NV shortcut)
- Make sure to check “Add this data to the Data Model” option before clicking ok.
- From your pivot table field list, switch to ALL view.
- Add both (or all fields) to row label area.
- Now, change the pivot table layout to “Show in tabular form” and check “Repeat all item labels” option.
- Turn off sub totals & grand totals.
- Viola, your cross product is ready. All combinations are generated by Excel for you. Use them as you see fit.