Splitting Single Column Into Multiple Columns 1000 Rows Per Column

I have an extremely large column of data that I want to split into multiple columns of 1000 rows each. I have combed the internet for any possible answer, and am unable to find a

Solution 1:

=ARRAYFORMULA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(A2:A)/3),3,ROW(A2))),{ROW(A2:A),A2:A},2,0))

• SEQUENCE to create a sequential array of numbers with 3 columns

2   3   4
5   6   7
8   9   10

• TRANSPOSE it to make a array with 3 rows counting from top to bottom:

2   5   8
3   6   9
4   7   10

• VLOOKUP the created array to substitute artificial created numbers to their row values using a artificially created array: {ROW(A2:A),A2:A}

Red     Green   Magenta
Blue    Pink    Brown
Yellow  Orange  Black

Solution 2:

Try this:

functiononetonine() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const vs=sh.getDataRange().getValues().map(function(r){return r[0];});
  var a=[];  
  var b=[];
  vs.forEach(function(e,i){
    if(i%9==0) {
      b=[];
    }
    b.push(e);
    if(i%9==8) {
      a.push([b[0],b[3],b[6]],[b[1],b[4],b[7]],[b[2],b[5],b[8]]);
      Logger.log('a:%',a);
    }
    
  });
  Logger.log('final:%s',a);
  sh.getRange(sh.getLastRow()+1,1,a.length,3).setValues(a);
}

Data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Results:

1,4,7
2,5,8
3,6,9
10,13,16
11,14,17
12,15,18
19,22,25
20,23,26
21,24,27

Solution 3:

Alternatively, if want to go the script route, you will need two utilities:

1. That splits the array of cell values into chunks of size 3 (to get the desired amount of rows).
2. That transposes cell values to 3 rows from the chunks given.

See snippet below for a working implementation:

/**
 * @typedef {object} ChunkifyConfig
 * @property {number} [size]
 * @property {number[]} [limits]
 * 
 * @summary splits an array into chunks
 * @param {any[]} source 
 * @param {ChunkifyConfig}
 * @returns {any[][]}
 */constchunkify = (source, { limits = [], size } = {}) => {

    const output = [];

    if (size) {
        const { length } = source;

        const maxNumChunks = Math.ceil((length || 1) / size);
        let numChunksLeft = maxNumChunks;

        while (numChunksLeft) {
            const chunksProcessed = maxNumChunks - numChunksLeft;
            const elemsProcessed = chunksProcessed * size;
            output.push(source.slice(elemsProcessed, elemsProcessed + size));
            numChunksLeft--;
        }

        return output;
    }

    const { length } = limits;

    if (!length) {
        return [Object.assign([], source)];
    }

    let lastSlicedElem = 0;

    limits.forEach((limit, i) => {
        const limitPosition = lastSlicedElem + limit;
        output[i] = source.slice(lastSlicedElem, limitPosition);
        lastSlicedElem = limitPosition;
    });

    const lastChunk = source.slice(lastSlicedElem);
    lastChunk.length && output.push(lastChunk);

    return output;
};

const test = [
  ["Red"],["Blue"],["Yellow"],["Green"],["Pink"],["Orange"],["Magenta"],["Brown"],["Black"]
];

const chunked = chunkify(test, { size : 3 })
  .map(chunk => chunk.reduce((a,c) => ([...a, ...c]), []))
  .reduce((acc,curr) => {
  
    curr.forEach((row,ri) => {
      const cells = acc[ri] || [];
      cells.push(row);
      acc[ri] = cells;
    });
    
    return acc;
  
  }, []);


console.log(chunked);

---

Note the direct dependency between number of rows and the size of chunks.