Generate all possible combinations for Columns(cross join or Cartesian product)

by

in post-pandemic new world we can solve this with:

=INDEX(FLATTEN(A2:A3&" "&TRANSPOSE(B2:B4)))

enter image description here

to account for future expansion we can do:

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

enter image description here

for 3 columns:

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

enter image description here

4 columns:

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

enter image description here

for more see: https://stackoverflow.com/a/74160711/5632629

More Related Contents:

  1. QUERY and order by the first date of column
  2. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  3. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  4. Import data into Google Colaboratory
  5. Conditional formatting based on another cell’s value
  6. Google Sheets importXML Returns Empty Value
  7. Get the last non-empty cell in a column in Google Sheets
  8. How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
  9. How to SUM duration in Google Sheets?
  10. Conditional Formatting from another sheet
  11. New Google Spreadsheets publish limitation
  12. ArrayFormula and “AND” Formula in Google Sheets
  13. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  14. Query is ignoring string (non numeric) value
  15. Repeat whole row N times based on column value in Google Sheets
  16. I get Formula parse error with all formulas
  17. Download link for Google Spreadsheets CSV export – with Multiple Sheets
  18. IMPORTXML and right XPath from Bloomberg price [duplicate]
  19. What factors determine the memory used in lambda functions?
  20. How to list all permutations without repetition?
  21. How to use arrayformula with formulas that do not seem to support arrayformulas?
  22. Repeat each row N times in Google Sheets
  23. How to create all possible pair combinations without duplicates in Google Sheets?
  24. Col BY causing parse error in Google sheets query
  25. Count cells that contain any text
  26. How to make a range repeat n-times in Google SpreadSheet
  27. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  28. Google Sheet API v4
  29. How to Print sheet/range using .gs script in Google Sheets?
  30. Google Sheets QUERY Function: Select Columns by Name

Leave a Comment