Generate all possible combinations for 3 Columns in Google Sheets

Question

I have a Google Spreadsheets with four columns.

I need to populate the Column D with all the possible combinations of the values in Columns A and C. Please take a look a the capture to see what I mean.

I found this following formula, but it use for 2 column.but i have 3 column.

i am using this code:

=ArrayFormula(transpose(split(concatenate(rept(A1:A&char(9),counta(B1:B))),char(9)))&" "&transpose(split(rept(concatenate(B1:B&char(9)),counta(A1:A)),char(9))))

Does this answer your question? [How to create all possible pair combinations without duplicates in Google Sheets?](https://stackoverflow.com/questions/47922267/how-to-create-all-possible-pair-combinations-without-duplicates-in-google-sheets) — TheMaster, Apr 27 '20 at 11:58

score 2 · Answer 1 · answered Oct 30 '21 at 20:26

2

use:

=INDEX(FLATTEN(FLATTEN(
 FILTER(A2:A; A2:A<>"")&" "&TRANSPOSE(
 FILTER(B2:B; B2:B<>"")))&" "&TRANSPOSE(
 FILTER(C2:C; C2:C<>""))))

answered Oct 30 '21 at 20:26

player0

124,011
12
67
124

score 1 · Accepted Answer · edited Nov 05 '21 at 09:29

Answer:

You can pass this formula back into itself to get the same result for three columns.

Formula:

=ArrayFormula(transpose(split(concatenate(rept(A1:A&char(9),counta(ArrayFormula(transpose(split(concatenate(rept(B1:B&char(9),counta(C1:C))),char(9)))&" "&transpose(split(rept(concatenate(C1:C&char(9)),counta(B1:B)),char(9))))))),char(9)))&" "&transpose(split(rept(concatenate(ArrayFormula(transpose(split(concatenate(rept(B1:B&char(9),counta(C1:C))),char(9)))&" "&transpose(split(rept(concatenate(C1:C&char(9)),counta(B1:B)),char(9))))&char(9)),counta(A1:A)),char(9))))

This formula replaces the references to B1:B to a formula which transposes all combinations of column B and column C, and transposes all those combinations with those in column A.

Generate all possible combinations for 3 Columns in Google Sheets

2 Answers2

Answer:

Formula:

Linked