Generate all possible combinations of values in two columns (i.e. cartesian product)
There are many situations in which it can be useful to generate all possible combinations for two columns of values. This is done by taking every value in the first column and pairing it with every value in the second column.
Contents
Method
 Create data in columns A and B
 In C1, enter the following formula:

=transpose(split(join("", arrayformula(rept(filter(A1:A, len(A1:A))&char(9999), counta(B1:B)))), char(9999)))

 In D2, enter the following formula:

=transpose(split(rept(join(char(9999), filter(B1:B, len(B1:B)))&char(9999), counta(A1:A)), char(9999)))

 This will results in all possible combinations of column A and B being placed into columns C and D, as shown in the above picture.
 Columns C and D can then be manipulated in any way. See below for some example use cases.
Demonstration of this method: https://docs.google.com/spreadsheets/d/1OofQdn8iQR44Mmdo3qxNhHPQ7KScgDu4g8AwrH8I8bc/edit#gid=0
Example Use Cases
 Generating ideas for website names
 A common strategy for generating website names is to combine two words together. Create a column of first words, a column of second words, and then combine them together to see a list of ideas.
 Example: https://docs.google.com/spreadsheets/d/1OofQdn8iQR44Mmdo3qxNhHPQ7KScgDu4g8AwrH8I8bc/edit#gid=1554192607
 Creating a list of unique items for inventory management
 Say you’re selling tshirts and have a variety of styles, colors, and sizes. You’ll need to create a list of unique items in order to manage your inventory.
 Create a column of styles, a column of colors, and a column of sizes, then combine them together to get a list of all your unique items.
 This example combines 3 columns by producing intermediary results. There are more advanced methods for doing this in one step, but we’ll leave that for another tutorial.
 Example: https://docs.google.com/spreadsheets/d/1OofQdn8iQR44Mmdo3qxNhHPQ7KScgDu4g8AwrH8I8bc/edit#gid=1648590663
not working for me! i’ve tried changing language but won’t work either. i’m copying exactly the same example and my google sheets will output error
Jano, can you share your sheet? Be glad to check it out.