# Join two columns in Google Sheets

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 !! Commented Feb 17, 2015 at 19:40

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 ?

• This would be an awesome answer if you could explain what's happening in your function call.
– ale
Commented Feb 17, 2015 at 21:06
• I added an explanation in the sheet I shared. If you still have question, don't hesitate to ask.
– JPV
Commented Feb 17, 2015 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
Commented Feb 17, 2015 at 22:33
– JPV
Commented Feb 17, 2015 at 22:45
• 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. Commented Apr 3, 2015 at 14:11

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
* @return A cross join of the two ranges
* @customfunction
*/
var output = [];
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;
}
``````

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.

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

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

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.