I have a big sheet with lot of data. I extract a column from that sheet with certain condition using filter() or query() or vlookup(). Then the result will be a single column with long data/row. For example : the output is a single column with 300 rows but i want 50 row per column. Is there any method split them automatically into 6 column (each contains 50 rows) rather than having a single column contains 300 rows.
Thanks
While this may be possible with formulae, even player0's brilliant and complex function has an upper limit to how much input it can handle. To me it seems like a perfect situation for an Apps Script custom function.
function divideColumn(input, numberToSplit){
const length = input.length
// The input it receives in the form of a two dimensional array. This just makes it one dimensional for convenience
const flattenedInput = input.flat()
// Getting the number of columns that will be filled
const numberOfColumns = Math.ceil(length / 50)
// Building the 2D array output with the specified number of rows.
const output = []
for (let i=0; i!= numberToSplit; i++){
output.push([])
}
// Building an intermediate 2D array of the columns
const columns = []
for (let j=0; j!=numberOfColumns; j++){
const lowerBound = numberToSplit * j
let upperBound;
if (j != numberOfColumns - 1) {
upperBound = numberToSplit * (j + 1)
} else {
upperBound = -1
}
const col = flattenedInput.slice(lowerBound, upperBound)
columns.push(col)
}
// Transposing the intermediate array into the final output format
columns.forEach((col) => {
col.forEach((val, idx) => {
output[idx].push(val)
})
})
return output
}
In use you can do this:
It will only work with a single column.
Menu > Tools > Script Editor
Past the function there and save.
Then you should be ready to go!
