I'm looking for a way to repeat records/rows based on two different tables, like below:
TABLE 1:
Col1 Col2
A 101
B 102
C 103
TABLE 2:
Col3
1
2
3
REPEATING RESULTS:
Col1 Col2 Col3
A 101 1
B 102 1
C 103 1
A 101 2
B 102 2
C 103 2
A 101 3
B 102 3
C 103 3
In 'Microsoft Access' or SQL i can do it very quickly, by using SQL select string:
SELECT table1.column1, table2.column1
FROM table1, table2
but i'm wondering if there is any way to do it in google sheets with QUERY function etc.
You can accomplish this via a custom function created in Google Apps Script. To achieve this, follow these steps:
function REPEAT(table1, table2) {
var output = [];
output.push([]);
for (var i = 0; i < table1[0].length; i++) {
output[0].push(table1[0][i]);
}
for (var i = 0; i < table2[0].length; i++) {
output[0].push(table2[0][i]);
}
for (var i = 1; i < table2.length; i++) {
for (var j = 1; j < table1.length; j++) {
output.push([]);
for (var k = 0; k < table1[j].length; k++) {
output[output.length - 1].push(table1[j][k]);
}
for (var k = 0; k < table2[i].length; k++) {
output[output.length - 1].push(table2[i][k]);
}
}
}
return output;
}
I hope this is of any help.
Great answer!! Works great. PS. in this function the first row from tables will be used for headers only.