Equally divide a list of names in Google Sheets

Question

We are using Google Sheets to track a list of about 60 people. We add and remove some each week. I am using functions like this to join them into a groups, =TEXTJOIN(", ", TRUE,$A$2:$A$15)

I would like to replace the $A$2:$A$15 part with something that would take 1/5th of the total list regardless of how long or short it becomes. Is this possible?

score 1 · Answer 1 · answered Nov 05 '20 at 22:59

1

try:

=TEXTJOIN(", ", 1, INDIRECT("A2:A"&ROW(A2)-1+ROUNDDOWN(COUNTA(A2:A)/5)))

answered Nov 05 '20 at 22:59

player0

124,011
12
67
124

score 1 · Answer 2 · answered Nov 05 '20 at 23:06

1

or:

=TEXTJOIN(", ", 1, 
 QUERY(A2:A, "where A is not null limit "&ROUNDDOWN(COUNTA(
 QUERY(A2:A, "where A is not null", 0))/5), 0))

answered Nov 05 '20 at 23:06

player0

124,011
12
67
124

score 1 · Accepted Answer · answered Nov 05 '20 at 23:25

If you want to group only the first fifth:

=TEXTJOIN(", ", TRUE,$A$2:INDEX($A$2:$A,ROUNDUP(COUNTA(A2:A)/5)))

or if you want group all:

=TEXTJOIN(", ", TRUE,FILTER($A:$A,ROW($A:$A)>=(ROUNDUP(COUNTA($A:$A)/5)*(ROW()-2)+2),ROW($A:$A)<=(ROUNDUP(COUNTA($A:$A)/5)*(ROW()-1)+1)))

Equally divide a list of names in Google Sheets

3 Answers3