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.

  • Stick-a-round Jeff, answers might take a while...Welcome on Web Applications !! – Jacob Jan Tuinstra 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 ?

| improve this answer | |
  • 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. – Sherwood Botsford 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.

| improve this answer | |
  • Nice answer. The code is clear even to someone like me who does write google script yet. – Sherwood Botsford Apr 3 '15 at 14:13
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

| improve this answer | |

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.