1

I'm not sure if this would require a custom function of some sort or can be done with builtin functions

If I have some columns

   Item  | Section
a        |  1
b        |  1
c        |  2
d        |  1
e        |  2

and then I want to make a column that dynamically looks like:

Section 1
a
b
d
Section 2
c
e

That column has columns to the left and right that shouldn't change. Just the number of cells for each section should be dynamic based on the data from the first columns, so as things are moved from one section to another the sections resize themselves

1

If you don't have many possibilities for the Section column, this can be a viable option :

={
B1&ROW(A1);FILTER(A:A,B:B=row(A1));
B1&ROW(A2);FILTER(A:A,B:B=row(A2));
B1&ROW(A3);FILTER(A:A,B:B=row(A3))
}

Example here

enter image description here


If there are many many many sections, you can use this formula who'll create the array you need :

="={"&textjoin(";",true,ArrayFormula("B1&ROW(A"&row(indirect("A1:A"&MAX(B:B)))&");FILTER(A:A,B:B=ROW(A"&ROW(indirect("A1:A"&MAX(B:B)))&"))"))&"}"

Then copy and paste inside a cell (the content, not the formula)

This is the kind of example it can create :

={
B1&ROW(A1);FILTER(A:A,B:B=ROW(A1));
B1&ROW(A2);FILTER(A:A,B:B=ROW(A2));
B1&ROW(A3);FILTER(A:A,B:B=ROW(A3));
B1&ROW(A4);FILTER(A:A,B:B=ROW(A4));
B1&ROW(A5);FILTER(A:A,B:B=ROW(A5));
B1&ROW(A6);FILTER(A:A,B:B=ROW(A6));
B1&ROW(A7);FILTER(A:A,B:B=ROW(A7));
B1&ROW(A8);FILTER(A:A,B:B=ROW(A8))
}
| improve this answer | |

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.