Combination table of elements from another table

user_15671668 0
Viewed 40

I have a table of arguments which looks like this:

argument 1 argument 2 min max
elem1 elem2 elemA 8 15
elem1 elem2 elem3 elemB elemC elemD elemE 16 32
elem1 elem2 elem3 elemF elemG elemH elemI 39 42
elem4 elemF elemG elemH elemI 42 83

Elements in first and second column are strings, for example:

elem1 = 'Rio de Janeiro', elem2 = 'Tokio', elemA='New York' and so on...

Min and max columns are ranges and can be both treated as argument 3.

I am trying to write down a table of every possible combination of elements from argument 1 column with arguments 2 and 3, like this:

argument 1 argument 2 min max
elem1 elemA 8 15
elem2 elemA 8 15
elem1 elemB 16 32
elem1 elemC 16 32
elem1 elemD 16 32
elem1 elemE 16 32
elem2 elemB 16 32
elem2 elemC 16 32
elem2 elemD 16 32
elem2 elemE 16 32
elem3 elemB 16 32
elem3 elemC 16 32
elem3 elemD 16 32
elem3 elemE 16 32
elem1 elemF 39 42
elem1 elemG 39 42
elem1 elemH 39 42
elem1 elemI 39 42
elem2 elemF 39 42
elem2 elemG 39 42
elem2 elemH 39 42
elem2 elemI 39 42
elem3 elemF 39 42
elem3 elemG 39 42
elem3 elemH 39 42
elem3 elemI 39 42
elem4 elemF 42 83
elem4 elemG 42 83
elem4 elemH 42 83
elem4 elemI 42 83

I have no idea what formulas should I use.

google-sheets-formula google-sheets
3 Answers

In the example data, elem1 elem2 uses a space as separator, but Rio de Janeiro also includes spaces. It is unclear whether the data is in a format that can be parsed programatically.

In the event the real data does not include strings like Rio de Janeiro, or you are using separators other than spaces, try something like this to get started:

=arrayformula( split( query( flatten( split(A1:A10, " ") & "|" & B1:B10 & "|" & C1:C10 ), "where not Col1 starts with '|' ", 0 ), "|" ) )

Replace the " " with the separator character you are using.

The formula will expand column A but not column B. To expand column B as well, apply a similar formula to the results from the first one.

user_130451930

I am not sure if what I did will work for you, but it seemed to have worked with the test data that you have uploaded. It is not one formula, but rather a few that automatically update the "Final Result" when you change the test data.

Here is the link to the spreadsheet.

The formula in the first column under final result would be enough to get all of the combinations, but there is a hard cap of 50,000 characters on the CONCAT formula, which I do not know how to bypass.

EDIT:

I have changed the calculator to match what you were trying to achieve: enter image description here

You will find the new version on the "EDIT" tab.

If you are using data that is different from the "Test Data" that is referenced in the formulas, just make sure to change around the references in the formulas to fit your new data. I have also added these instructions in the spreadsheet.

user_131969950
user_156716680
Related