0

I'm looking for a formula that lets me take a dynamic column range and tell it to spill into the next column if it exceeds a certain number of rows.

The most rows I want in one column is 10,000. The results can be dynamic so I need the formula to be dynamic as well. I.e. if I have 25,000 rows in the original dataset, the formula should output 2 columns with 10,000 and 1 column of 5,000 of those results.

The same formula should be able to produce 4 columns of 10,000 rows from an original data set of 40,000 rows.

Here is a simplified example:

enter image description here

Similarly, the formula could flex to this:

enter image description here

4
  • Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Sep 8 at 19:42
  • Share a link to a sample/test spreadsheet containing exactly the information shown in your post. Be sure to set the link's Share permissions (when you create the link) to "Anyone with the link..." and "Editor." This will be the most efficient and effective to test and then share a solution with you.
    – Erik Tyler
    Sep 9 at 3:02
  • Hi. Have you looked at Split 1 column into multiple columns with up to 500 cells each on StackOverflow. This is EXACTLY what you are looking for.
    – Tedinoz
    Sep 13 at 3:01
  • Regarding "Split 1 column into multiple columns with up to 500 cells each" - while this EXACTLY reflects your scenario, the preferred answer is not suited to the volume of your records. In testing, 100 records is processed very quickly; but 60,000 records takes 4 minutes to recalculate and requires 8000+ columns to be inserted (Yes, columns! even though the final number of columns would be seven). In short, it is not very efficient. There are other answers on the Q&A that you might evaluate. In short, though the scenario is the same as yours, the number of records may make it incompatible.
    – Tedinoz
    Sep 13 at 4:26
0

I created test data of 60,000 text records in Column A of a Google spreadsheet. Then I applied the preferred answer from Split 1 column into multiple columns with up to 500 cells each. It took 4+ minutes for the spreadsheet to recalculate, and even then it required the insertion of over 8,000 columns (even though the answer would required display of 7 (seven) columns. I judged this to be unacceptable.

With a view to creating a quick-and-dirty solution, I created a solution based on queries. All values are created treated as variables, and recalculation time is negligible.

QUERY

=if(F1<=$C$7,query({indirect("A1:"&address($C$1,1,4))}, "select Col1 limit "&$C$2&" offset "&$C$2*(F1-1)),)

  • the initial query is in cell F3 in the screenshot, and is manually copied to cell G3:L3.
  • Cells F1:M1 contain a sequence running from 1 to N
  • Cells F2 contains: =if(F1<=$C$7,"Col"&F1,), and is manually copied to cell G2:L2.

CRITERIA

Column C contains some key calculations and one manual entry value (the max number of records per row). The formula are displayed in Column C in the screenshot (using FORMULATEXT)


criteria


SAMPLE DATA (extract)

Note: The same data are text values, even though they consistent of numeric characters.

Sample data


OUTPUT

output

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.