I have a sequence of letters, in Google Spreadsheets:

  | A   B   C   D   E   F
--|----------------------
1 | U   Y   0   4   X   N
2 | H   X           Y   
3 | W

The first row contains a character pattern that serves as a template. Each subsequent row contains variants of the values in some of the columns. The first row values are used for columns that don't contain variants.

There are between 1 and 2 variants per column that has variants, and these columns do not necessarily have the same number of variants. In the future, the problem could potentially have three or more variants per column.

How can I get all variations of the sequence? Such as:

UY04XN
HY04XN
UY04YN
HY04YN
...

I believe this is something to do with 'Cartesian Products', and I suppose the SQL would be roughly of the form:

SELECT table1.column1, table2.column2...
FROM  table1, table2...

... but I have no idea how that would translate into flexible spreadsheet function code.

up vote 3 down vote accepted

Short answer

QUERY() allow the use of a subset of SQL but this subset does not include the FROM clause.

QUERY() require a single array as the source table, so it could not be used, instead use ARRAYFORMULA(), TRANSPOSE(), JOIN(), SPLIT(), REPT(), FILTER(), COUNTA(), SORT() and the division (/) and concatenate (&) operators to build a "flexible formula" as its performance is better than a custom function.

Procedure

Assuming that for "flexible spreadsheet function code" the OP mean a formula,

  1. To make things simpler, add an apostrophe before each number value in order to format them as text.

Note: Other way, it will be required to use COUNT instead of COUNTA for numeric columns, and this will make the following procedure less flexible as this will only works in the "future" if all the column values added later are numbers.

  1. Calculate the size for the cartesian product.

H1:

=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D)*COUNTA(E:E)*COUNTA(F:F)

Note: There are five columns to match the sample data provided by the OP.

  1. Calculate the cartesian product:

J1:

=ArrayFormula(
  {
     SORT(
       TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(A:A,LEN(A:A))))&",",$H$1/COUNTA(A:A)),","))
       &TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(B:B,LEN(B:B))))&",",$H$1/COUNTA(B:B)),","))
    ,1,TRUE)           
    &TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(C:C,LEN(C:C))))&",",$H$1/COUNTA(C:C)),","))
    &TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(D:D,LEN(D:D))))&",",$H$1/COUNTA(D:D)),","))         
    &TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(E:E,LEN(E:E))))&",",$H$1/COUNTA(E:E)),","))
    &TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(filter(F:F,LEN(F:F))))&",",$H$1/COUNTA(F:F)),","))
  }
)

Explanation

f1(A): $H$1/COUNTA(A:A) number of times that the A column values should be repeated.

f2(A): filter(A:A,LEN(A:A)) values in column A.

f3(A): TRANSPOSE(split(REPT(JOIN(",",TRANSPOSE(f2))&",",f1),",")) repeats the column values

f4(A-B): SORT(f3(A)&f3(B),1,TRUE) Concatenates the first pair of columns and sort them in ascending order.

f5(): f4(A-B)&f3(C)&f3(D)&f3(E)&f3(F) Concatenates the other columns rows. As only one of this columns has more than one element, it's not required to sort them again.

Note: There is one f3() formula for each column. There are five to match the sample data provided by the OP.

f6(): ArrayFormula(f5()): "Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays"1.

Demo spreadsheet

References

This finds permutations for 3 rows, columns A:L. See results in yellow. Just drag formula in M2 across to accommodate new columns. More rows can be added to the formula, but will be quite slow I'm guessing.

I'm sure there are better ways to do this, but it seems to do the trick.

sample sheet

  • 4
    Your answer would be significantly better if the salient information was contained in the answer itself, with the sample sheet used only as supplemental information. We like answers to be self-contained; people shouldn't have to go elsewhere to get the information. Further, if some time down the road you stop sharing the document, this answer becomes absolutely worthless. – ale Nov 12 '16 at 18:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.