How to autofill repeating series in Google Sheets

Question

I'm trying to work out how to AutoFill the following data into Google Sheets.

| =A1 |  
| =B1 |  
| =C1 |  
| =A2 |  
| =B2 |  
| =C2 |  
| =A3 |  
| =B3 |  
| =C3 | 

Any help much appreciated!

Answer 1

Modular arithmetic helps, together with the offset function:

=offset($A$1, (row(A1)-1)/3, mod(row(A1)-1, 3))

As written above, this formula is equivalent to =A1 because both offset values are 0. But when it's copied down the column, it becomes

=offset($A$1, (row(A2)-1)/3, mod(row(A2)-1, 3))

which evaluates to =offset($A$1, 1/3, 1), equivalent to =B2, because the offset is by 0 rows and 1 columns (fractional offsets are truncated to integer). Two rows down we get

=offset($A$1, (row(A4)-1)/3, mod(row(A4)-1, 3))

which is =A2 because the offset is by 1 row and 0 columns.

Answer 2

In the, perhaps unlikely, event that you want what you show you might copy down from Row1:

="="&char(65+mod(row()-1,3))&int((2+row())/3)

select the relevant column, Ctrl+V, Ctrl+Shift+V, then replace = with =.

This will extend the A,B,C series as A,B,C and the numeric to 4s, 5s, 6s ...

CHAR
MOD
ROW