Moving values forward and wrapping in a table

[Buzzcut]

Hi Team,
I've exhausted both my Excel knowledge, tinkering skills and Google.
I have a list of 400 students I need to schedule for exams at 5 different stations. I need to take the first row of 5 and move them forward one place in row 2, #5 wrapping round to #1 in row 2.
In row 3 I need to take 6-10 and do the same, so row 4 contains those names moved on one place with the wrap.
I can list the students with an index so I need something like this for the lookup, I can't seem to get my brain around the OFFSET/ROW formula:

1	2	3	4	5
5	1	2	3	4
6	7	8	9	10
10	6	7	8	9
11	12	13	14	15
15	11	12	13	14

Any help would be amazing!
Cheers...Buzz

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 how about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	Rossendale		Rossendale	Bradford	Sheffield	Kirklees	Calderdale
3	Bradford		Calderdale	Rossendale	Bradford	Sheffield	Kirklees
4	Sheffield		Eden	Barnsley	Cornwall	Burnley	Bolton
5	Kirklees		Bolton	Eden	Barnsley	Cornwall	Burnley
6	Calderdale		Dudley	Wycombe	High Peak	Wealden	Dacorum
7	Eden		Dacorum	Dudley	Wycombe	High Peak	Wealden
8	Barnsley		Leeds	Harrogate	Birmingham	Tandridge	Craven
9	Cornwall		Craven	Leeds	Harrogate	Birmingham	Tandridge
10	Burnley
11	Bolton
12	Dudley
13	Wycombe
14	High Peak
15	Wealden
16	Dacorum
17	Leeds
18	Harrogate
19	Birmingham
20	Tandridge
21	Craven
Main

Cell Formulas
Range	Formula
C2:G9	C2	=INDEX(A2:A21,SORTBY(VSTACK(SEQUENCE(4,5),SORTBY(SEQUENCE(4,5),{2,3,4,5,1})),VSTACK(SEQUENCE(4,,,2),SEQUENCE(4,,2,2))))
Dynamic array formulas.

 

[Buzzcut]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 how about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	Rossendale		Rossendale	Bradford	Sheffield	Kirklees	Calderdale
3	Bradford		Calderdale	Rossendale	Bradford	Sheffield	Kirklees
4	Sheffield		Eden	Barnsley	Cornwall	Burnley	Bolton
5	Kirklees		Bolton	Eden	Barnsley	Cornwall	Burnley
6	Calderdale		Dudley	Wycombe	High Peak	Wealden	Dacorum
7	Eden		Dacorum	Dudley	Wycombe	High Peak	Wealden
8	Barnsley		Leeds	Harrogate	Birmingham	Tandridge	Craven
9	Cornwall		Craven	Leeds	Harrogate	Birmingham	Tandridge
10	Burnley
11	Bolton
12	Dudley
13	Wycombe
14	High Peak
15	Wealden
16	Dacorum
17	Leeds
18	Harrogate
19	Birmingham
20	Tandridge
21	Craven
Main

Cell Formulas
Range	Formula
C2:G9	C2	=INDEX(A2:A21,SORTBY(VSTACK(SEQUENCE(4,5),SORTBY(SEQUENCE(4,5),{2,3,4,5,1})),VSTACK(SEQUENCE(4,,,2),SEQUENCE(4,,2,2))))
Dynamic array formulas.

Wow, it's times like this I realise my Excel knowledge is low-level.
I'm using 365 so this is absolutely perfect.
Thankyou so much for the reply, this has helped me out no end.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Buzzcut]

You're welcome & thanks for the feedback.

Apologies, if you get a second could you explain exactly what's going on with the final 2 VSTACK SortBy?
I've disected as much as I can, and with F9 I can work through it but I get to this and I'm stumped by how ths gives the correct final array.

SORTBY(
{1,2,3,4,5;6,7,8,9,10;11,12,13,14,15;16,17,18,19,20;5,1,2,3,4;10,6,7,8,9;15,11,12,13,14;20,16,17,18,19},
{1;3;5;7;2;4;6;8}
)

Strangely enough it also works with SEQUENCE(4,,,,2) at the end instead of SEQUENCE(4,,2,2) - so {1;3;5;7;1;3;5;7}
In my mind the rows should be sorted {1;5;2;6;3;7;4;8}?

 

[Fluff]

The 1st VSTACK returns the data in cols C:G & the 2nd returns the values in col I

Fluff.xlsm
	C	D	E	F	G	H	I
1
2	1	2	3	4	5		1
3	5	1	2	3	4		3
4	6	7	8	9	10		5
5	10	6	7	8	9		7
6	11	12	13	14	15		2
7	15	11	12	13	14		4
8	16	17	18	19	20		6
9	20	16	17	18	19		8
Main

Cell Formulas
Range	Formula
C2:G9	C2	=SORTBY(VSTACK(SEQUENCE(4,5),SORTBY(SEQUENCE(4,5),{2,3,4,5,1})),VSTACK(SEQUENCE(4,,,2),SEQUENCE(4,,2,2)))
I2:I9	I2	=VSTACK(SEQUENCE(4,,,2),SEQUENCE(4,,2,2))
Dynamic array formulas.

The SORTBY then sorts the data so that row 6 moves up to become the 2nd row in the array, & row 7 becomes the 4th row in the array etc.