4

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.

1
  • Stick-a-round Jeff, answers might take a while...Welcome on Web Applications !! Feb 17 '15 at 19:40
5

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 ?

8
  • 1
    This would be an awesome answer if you could explain what's happening in your function call.
    – ale
    Feb 17 '15 at 21:06
  • I added an explanation in the sheet I shared. If you still have question, don't hesitate to ask.
    – JPV
    Feb 17 '15 at 22:31
  • Which is great, but what happens when that sheet is no longer available? We like our answers to be self-contained.
    – ale
    Feb 17 '15 at 22:33
  • 1
    Added it to my answer. I hope that helps ?
    – JPV
    Feb 17 '15 at 22:45
  • 1
    A good example of inscrutable spreadsheet code. <grin> This is a limitation of spreadsheets. Rule of thumb: If you have more than 4 sets of parentheses your code is unmaintainable by non-wizards. Stack overflow for the reader. Apr 3 '15 at 14:11
1

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
enter image description here

outcome
enter image description here

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.

1
  • Nice answer. The code is clear even to someone like me who does write google script yet. Apr 3 '15 at 14:13
1

Directly from Google Documentation:

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

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.