Menu
I have a Google SpreadSheets doc with three columns A, B and C.
I need to populate the Column C with all the possible combinations of the values in Columns A and B. Please take a look a the capture to see what I mean.
I found this to be done in Excel, here, but it doesn't work in google spreadsheets.
The formula should be useful even for more columns (e.g.: four instead of two)
Can I do this?
705
To do this, type the list of numbers into a cell in Google Sheets, and then use the permutation function to create a list of all the possible permutations. The function is =permutation(x,y) where "x" is the original list of numbers and "y" is the number of possible permutations.
Enter the formula =List1. Expand out the new List1 column and then Close & Load the query to a table. The table will have all the combinations of items from both lists and we saved on making a custom column in List1 and avoided using a merge query altogether!
You can't use it. The Google Sheets source code does seem to have a (perhaps partial?) implementation of LAMBDA , but there is no way to use it.
Update 201810
Original formula crashes for a big dataset. I described a way to make cross-join with any size of data here.
Try formula:
=ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9))) &" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9)))) The result:
car red train red car yellow train yellow car blue train blue You may use it again to add another list:
The formula is in cells C2 and E2,
C2 is:
=ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))&" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))) ) and E2 is:
=ArrayFormula(transpose(split(rept(concatenate(C2:C&char(9)),counta(D2:D)),char(9)))&" "&transpose(split(concatenate(rept(D2:D&char(9),counta(C2:C))),char(9))) ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
