5

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.

2
6

Short answer

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

QUERY() requires 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 means a formula,

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

Note: Another way, would require to use COUNT instead of COUNTA for numeric columns. This will make the following procedure less flexible as this will only work in the "future" provided all of 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 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 sorts 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

7
  • I think this formula only works because the numbers of entries in all columns are coprime to each other; if the second column also contains 3 entries, it fails. The reason for the coprimality being relevant is because that guarantees that when the columns are just repeated, then they get shifted such that they will only reach the original shift after "rows in column 1" times "rows in column 2" combinations. In particular if there are c columns of r entries each, then we just get r different combinations (the ones we get by concatenating each row) and each is repeated r^c/r times.
    – G. Bach
    Aug 3 '20 at 23:02
  • @G.Bach Have you made a demo spreadsheet showing that it fails? I just made a copy of the demo spreadsheet included in the question, added an element to the second column, the formula works fine for me.
    – Rubén
    Aug 3 '20 at 23:17
  • I made a copy of your spreadsheet and added a third value to the second column, this no longer generates the cartesian product. Did you actually get all of the possible combinations? You should see some combinations repeating, and some not appearing at all. For example, if you add the value "Z" to the second column, no combination starting in "HZ" will appear.
    – G. Bach
    Aug 4 '20 at 7:16
  • @G.Bach : Do you have a better formula?
    – Rubén
    Aug 4 '20 at 14:35
  • Just to make sure, are you seeing the same result I'm seeing if you add the Z to the second column? And I found a different one elsewhere that seems to work, see here on SO.
    – G. Bach
    Aug 4 '20 at 16:31
-2

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

1
  • 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 agree to our terms of service, privacy policy and cookie policy

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