2

GOAL:

Split one column of items into two half-sized columns of items. So if there are originally 1 column by n rows I want two columns by (n ÷ 2) rows with exactly the same items as the 1 x n column.

PROBLEM:

I would like to split 1 column ( that contains vendor Invoice#'s ) into two rows. To clarify I don't want to split the contents of each cell in the column into two columns, I want to split the column itself in two.

  • There could be 1 or zero items, In this case no splitting should occur.
  • There could be any an even-number of items greater than 1 in the column. If the total number of items are even I would just like the column split evenly in two. I.e. if there are 18 items I want two columns of 9.
  • There could be any odd-number of items greater than 2 in the column. If the total number of items of the column are odd I would like two have the column split almost evenly in two such that one of the two newly created columns has 1 more additional item than the other one.

EXAMPLE:

THIS: 101 102 103 104 105

BECOMES: 101 104 102 105 103

CONTEXT:

I have a huge google-sheet with lots of rows/records of jobs that were completed. I currently use a FILTER formula to filter out all the jobs such that it matches a persons name, & their expected pay-date, and it shows only those jobs. The purpose of this is to generate a check-statement.

2

Assuming the data is in column B, the following two formulas do the job. In cell C1:

=filter(B1:B, row(B1:B) < 1 + max(filter(row(B:B), len(B:B))) / 2)

In cell D1:

=filter(B1:B, row(B1:B) >= 1 + max(filter(row(B:B), len(B:B))) / 2)

Explanation:

  1. Find the last row with data by max(filter(row(B:B), len(B:B)))
  2. Divided by 2 and use as a threshold for splitting the column: rows above it go to column C, rows below it go to column D.
  • Works well. I also adapted it to work in three columns (for learning purposes). Thank you for the explanation! – Xzila Mar 22 '16 at 19:07

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.