Join two columns in Google Sheets

Question

I have two columns in Google Sheets, and I would like the product of a cross-join:

Student
-------
Jake
John
Sally
Grace

Days
-----
1/1/15
1/2/15
1/3/15
1/4/15

For example, if these were tables in SQL, and I did a cross-join, I would get

Days   Students
------ --------
1/1/15 Jake
1/1/15 John
1/1/15 Sally
1/1/15 Grace
1/2/15 Jake
1/2/15 John
1/2/15 Sally
1/2/15 Grace
1/3/15 Jake
1/3/15 John
1/3/15 Sally
1/3/15 Grace
...

Is there a simple way to do this in Google Sheets? I need to use Google Sheets to track students switching between classrooms (In week 1 Jake is in classroom A, but in week 2 Jake is in Classroom B). The only way I can figure to do this is to expand a list of students per day as above. However I'm definitely open to other suggestions.

Answer 1

assuming you have the names in A2:A5 and the dates in B2:B5, try this formula:

=ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))})

Example sheet with formula in D2

explanation:

This formula consists of two parts, forming a literal array {first part, second part}. It is basically two times the same formula, applied one to the dates and once to the names.

1. repeat (rept command) the dates and the names with the number of days (rows(B2:B5)).
   Difference: the dates are repeated first and then joined, the names are first joined then repeated.
2. Everything is sucked in one cell with the join command and a char9 (tab) is added
3. The tab (char9) is then used to split the contents of that cell
4. the outcome of the split is transposed, so a column is generated

Hope that helps ?

Answer 2

Here's a custom formula that does the same.

Code

/**
 * Create a cross join of two ranges
 *
 * @param {range} students The range with student names
 * @param {range} days The range of dates
 * @param {range} header The range with the headers
 * @return A cross join of the two ranges
 * @customfunction
 */
function crossJoin(students, days, header) {  
  var output = [];
  if(header) {output.push([header[0][1], header[0][0]])};
  for(var i = 1, iLen = days.length; i < iLen; i++) {
    for(var j = 1, jLen = students.length; j < jLen; j++) {
      output.push([days[i][0], students[j][0]]);      
    }
  }
  return output;  
}

Screenshot

data

outcome

Explained

If a header has been selected, then add it first thing. Afterwards for all dates, all names are added. When all items are processed, then return the result.

Notes

The date output in Google Spreadsheet via a script is always considered a date. No formatting needed afterwards.

Example

I've created an example file for you: Join two columns in Google Sheets.
The script can be added from the main menu Tools > Script editor. Save the script and the custom formula can be used right-away.

Answer 3

Directly from Google Documentation:

=ArrayFormula(SPLIT(FLATTEN(A1:A3 & "|" & TRANSPOSE(B1:B2)), "|"))

Source:https://support.google.com/docs/answer/10307761?hl=en

Answer 4

I solved this by using Javascript LINQ (language integrated query).

=linq("Enumerable.from('Orders').take(100).join('People', '$.Region', '$.Region',(l, r)=> {return {...l,...r}})", State!$A$1)

It lets you specify Javascript with complex join conditions. You can also perform other SQL queries such as Grouping, Projecting, Sorting and Filtering your sheets as if they were database tables. Look at the links below.

Note that in the LINQ query language I replaced all spaces in column names with underscores to make them valid JS identifiers.

https://docs.google.com/spreadsheets/d/1DHtQlQUlo-X_YVfo-Wo-b7315sSk2pxL5ci4Y9lxvZo/edit?usp=sharing

https://script.google.com/d/1R5L2ReHJrBRwyoSoVOFLzEQZiGtxidPfPkAeVownt7SWX6TpacY7gA7j/edit?usp=sharing