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.

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

  1. Generating ideas for website names
  2. Creating a list of unique items for inventory management
    • Say you’re selling t-shirts 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

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *