Separate 1 column into 3 or more parts.

Copper Contributor

Separate 1 column into 3 or more parts.

I need to separate 1 column into 3 columns, such as top 1/3, middle 1/3, bottom 1/3. Or better yet, I would like to separate 1 column into "n" columns...i.e. top 1/4, next 1/4 and so on.

2 Replies

Re: Separate 1 column into 3 or more parts.

@Goodie1455  Try this:

``=LET(in,A1:A123,cols,B1,WRAPCOLS(SORT(in,,-1),ROUNDUP(ROWS(in)/cols,0),""))``

where A1:A123 is the array and B1 is the number of columns you want and the -1 inside the SORT is to go high to low but change to 1 (or delete) to go low to high

Re: Separate 1 column into 3 or more parts.

I completely agree with the idea but why not follow through and create a λ-function?

The formula is

``````GroupByRankλ(array, columns)
= LET(
rows,   1 + QUOTIENT(ROWS(arr) - 1, cols),
return, WRAPCOLS(SORT(arr, , -1), rows, ""),
return
)``````

Of course, it is longer but, eventually, I plan to follow @Craig Hatmaker and create really long, perfectly-documented formulas.