CODEWITHSUNDEEP
google-sheets
Md. Foysal
Md. Foysal

Reputation: 103

Generate all possible combinations for 3 Columns in Google Sheets

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))))

Upvotes: 1

Views: 563

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

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.

Upvotes: 1

player0
player0

Reputation: 1

use:

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

enter image description here

Upvotes: 2

Related Questions