How do I drag a JOIN formula in Google Sheets and ignore the rows that have been joined in the above-CodePudding

I want to create a tool in Google Sheets that allows someone to paste a large number of serial numbers into a column and it will automatically JOIN the serial numbers by groups of 80 and split them by ",".

The formula I have for the first cell is:

=JOIN(",",A1:A80)

This works fine, but I need to drag this formula down to facilitate 1000s of serial numbers. The problem I'm facing is that when I drag the formula down, it applies to A2:A81, then A3:A82 and so on. But what I need is A81:A160, then A161:A240 etc.

Does anyone know of an easy way to do this?

CodePudding user response：

One option would be to use:

=TEXTJOIN(",",1,QUERY(A:A,"limit 80 offset "&(ROW(A1)-1)*80))

Using TEXTJOIN() instead of JOIN() to avoid trailing commas when values run out at the end of A:A. See the example below with concatenating 3 consecutive values:

CodePudding user response：

a litle bit of math...

="A"&80*(ROW(A1)-1) 1&":A"&80*ROW(A1)

so try:

=JOIN(",", INDIRECT("A"&80*(ROW(A1)-1) 1&":A"&80*ROW(A1)))