Reshape a list: TAKE, DROP and CHOOSECOLS
Pull the top rows of a sorted list with TAKE, everything after them with DROP, and only the columns you need with CHOOSECOLS. Intermediate · 5 min · TAKE, DROP, CHOOSECOLS, Nesting dynamic arrays
Once a formula spills a list, three small functions reshape it without any helper columns:
TAKE(array, rows)keeps the first rows (a negative number keeps the last ones)DROP(array, rows)removes the first rows and keeps the restCHOOSECOLS(array, n, …)keeps only the columns you list, in the order you list them
They are at their best wrapped around SORT or FILTER. The sheet is a sales team's results for the quarter.
Steps
Who are the top three? In
E2, sort the table by sales, highest first, and keep only the first three rows:=TAKE(SORT(A2:C11, 3, -1), 3)Now everyone who is not in the top three. In
E7, drop the first three rows of the same ranking:=DROP(SORT(A2:C11, 3, -1), 3)The region is not needed in the summary. In
I2, keep only the first and third columns of the top three:=CHOOSECOLS(TAKE(SORT(A2:C11, 3, -1), 3), 1, 3)
Remember
TAKE(array, n)keeps the first n rows;TAKE(array, -n)the last n.DROP(array, n)removes the first n rows and keeps the rest.CHOOSECOLS(array, 1, 3)keeps those columns, in that order. CHOOSEROWS picks rows.- Wrap them around SORT or FILTER:
=TAKE(SORT(data, 3, -1), 3)is a live top three.
In the function reference: CHOOSECOLS, DROP, SORT, TAKE
Previous: SORTBY, SEQUENCE and LET · Next: Combine lists: VSTACK and HSTACK