staggering rows

[merlin777]

How can i stagger the contents of the following row using a formula? I want to stagger by different numbers of columns so row 2 might be identical data to row 1 but shifted right by a number of columns.

so for instance, if i stagger by 1 (always to the right) the first row will have 0 1 2 3 4 5 6 7 8 9, the next row will have 1 2 3 4 5 6 7 8 9 0, the third row will be 2 3 4 5 6 7 8 9 0 1 and so on
If I stagger by 2 it'll be 0123456789, 2345678901, 4567890123 and so on.

On a small scale I can do it by hand but this will be 365 columns and 10 rows and i want to try staggering by 0 to a max of 67 columns so id like a formula i can drag and fill!

Row 1 will always have the original data set.

I know there are functions which work with cell reference but i cant remember what they are called so its tricky to google them.

Thanks in advance!

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2	3	4	5	6	7	8	9
2	1	2	3	4	5	6	7	8	9	0
3	2	3	4	5	6	7	8	9	0	1
4	3	4	5	6	7	8	9	0	1	2
5	4	5	6	7	8	9	0	1	2	3
6	5	6	7	8	9	0	1	2	3	4
7	6	7	8	9	0	1	2	3	4	5
8	7	8	9	0	1	2	3	4	5	6
9	8	9	0	1	2	3	4	5	6	7
10	9	0	1	2	3	4	5	6	7	8
11	0	1	2	3	4	5	6	7	8	9
12	1	2	3	4	5	6	7	8	9	0
13	2	3	4	5	6	7	8	9	0	1
14	3	4	5	6	7	8	9	0	1	2
15	4	5	6	7	8	9	0	1	2	3
Sheet5

Cell Formulas
Range	Formula
A2:J15	A2	=INDEX($A$1:$J$1,MOD(ROWS($A$1:$A1)+COLUMNS($A1:A1)-1,COLUMNS($A$1:$J$1))+1)

 

[merlin777]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2	3	4	5	6	7	8	9
2	1	2	3	4	5	6	7	8	9	0
3	2	3	4	5	6	7	8	9	0	1
4	3	4	5	6	7	8	9	0	1	2
5	4	5	6	7	8	9	0	1	2	3
6	5	6	7	8	9	0	1	2	3	4
7	6	7	8	9	0	1	2	3	4	5
8	7	8	9	0	1	2	3	4	5	6
9	8	9	0	1	2	3	4	5	6	7
10	9	0	1	2	3	4	5	6	7	8
11	0	1	2	3	4	5	6	7	8	9
12	1	2	3	4	5	6	7	8	9	0
13	2	3	4	5	6	7	8	9	0	1
14	3	4	5	6	7	8	9	0	1	2
15	4	5	6	7	8	9	0	1	2	3
Sheet5

Cell Formulas
Range	Formula
A2:J15	A2	=INDEX($A$1:$J$1,MOD(ROWS($A$1:$A1)+COLUMNS($A1:A1)-1,COLUMNS($A$1:$J$1))+1)

Thanks for that - almost.
I need them to stagger to the right not left. If it helps i can limit the rows to 10 and we can start the data in column K to give some room to the left.
Also, my data will be alpha numeric, probably just letters. I apologise that my example implied just number 0 to 9.
And the number of columns staggered needs to be optional so i'll put the number of columns to stagger in a cell and change it as required.
Essentially, apart from the top data row, I need a cell to show the contents of the cell in the row above and N to the left, where N is the number of rows to stagger, if that makes sense?

 

[Eric W]

There was nothing in my original formula that required the values to be numbers. And I believe my example gave the same results as you mentioned in your post. Nevertheless, try this version:

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T
1	Offset		a	b	c	d	e	f	g	h	i	j
2	2		c	d	e	f	g	h	i	j	a	b
3			e	f	g	h	i	j	a	b	c	d
4			g	h	i	j	a	b	c	d	e	f
5			i	j	a	b	c	d	e	f	g	h
6			a	b	c	d	e	f	g	h	i	j
7			c	d	e	f	g	h	i	j	a	b
8			e	f	g	h	i	j	a	b	c	d
Sheet5

Cell Formulas
Range	Formula
K2:T8	K2	=INDEX($K$1:$T$1,MOD(ROWS($K$1:$K1)*$I$2+COLUMNS($K1:K1)-1,COLUMNS($K$1:$T$1))+1)

Note that you can use positive and negative numbers in the Offset value to make the offsets go in different directions.

 

[merlin777]

There was nothing in my original formula that required the values to be numbers. And I believe my example gave the same results as you mentioned in your post. Nevertheless, try this version:

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T
1	Offset		a	b	c	d	e	f	g	h	i	j
2	2		c	d	e	f	g	h	i	j	a	b
3			e	f	g	h	i	j	a	b	c	d
4			g	h	i	j	a	b	c	d	e	f
5			i	j	a	b	c	d	e	f	g	h
6			a	b	c	d	e	f	g	h	i	j
7			c	d	e	f	g	h	i	j	a	b
8			e	f	g	h	i	j	a	b	c	d
Sheet5

Cell Formulas
Range	Formula
K2:T8	K2	=INDEX($K$1:$T$1,MOD(ROWS($K$1:$K1)*$I$2+COLUMNS($K1:K1)-1,COLUMNS($K$1:$T$1))+1)

Note that you can use positive and negative numbers in the Offset value to make the offsets go in different directions.

Thanks, Eric I'll give that a try. I mistakenly assumed it was numeric because when i tried it, after 9 it returned 0, 1, 2 etc instead of 10, 11, 12.
Each subsequent row needs to stagger to the right so the +/- is great.

 

[merlin777]

I'm using the letter A to P to test and its more or less working except that it only goes up to J then starts again at A

e.g. (with an offset of +1)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

B

C

D

E

F

G

H

I

J

A

B

C

D

E

F

G

 

[Fluff]

@merlin777
What version of Excel are you using?
Please update your account profile to show this, as it affects which functions you can use.

 

[Eric W]

It works fine with a range that goes up to P, you just have to adjust the ranges in the formula to match your top row. I can come up with a version that looks for the last value in row 1 if you want, so you don't have to remember to change it. Or if you have Excel 365, Fluff can probably come up with a Spill version of the formula.

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Offset		a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p
2	-3		n	o	p	a	b	c	d	e	f	g	h	i	j	k	l	m
3			k	l	m	n	o	p	a	b	c	d	e	f	g	h	i	j
4			h	i	j	k	l	m	n	o	p	a	b	c	d	e	f	g
Sheet5

Cell Formulas
Range	Formula
K2:Z4	K2	=INDEX($K$1:$Z$1,MOD(ROWS($K$1:$K1)*$I$2+COLUMNS($K1:K1)-1,COLUMNS($K$1:$Z$1))+1)

 

[merlin777]

Thanks, Eric. I'll fiddle with it - i want to understand how it works, anyway. I'll come back if i'm struggling if i may?

 

[merlin777]

@merlin777
What version of Excel are you using?
Please update your account profile to show this, as it affects which functions you can use.

Hi Fluff. I'm 2007 and 2019. I updated my profile with 2007.