[Google Sheet Question] Trying to perform a "snake" report

[thp510]

I have a list of 5 individuals that I would like to assign using a snake draft (similar to a fantasy football style draft), where the the last person to pick something is the next person to pick. For example, there are 5 people (Kelly, Jon, Bob, Suzie, and Tom) and here's their pick order: Kelly goes 1st, Jon goes 2nd, Bob goes 3rd, Suzie goes 4th, and Tom goes 5th. However, next, Tom goes first, Suzie goes second, etc... Here's the first 16th pick order to show as an example:

1st Pick: Kelly
2nd Pick: Jon
3rd Pick: Bob
4th Pick: Suzie
5th Pick: Tom
6th Pick: Tom
7th Pick: Suzie
8th Pick: Bob
9th Pick: Jon
10th Pick: Kelly
11th Pick: Kelly
12th Pick: Jon
13th Pick: Bob
14th Pick: Suzie
15th Pick: Tom
16th Pick: Tom
...etc...etc... It's the snake method.

I want to create an equation where I can just populate the first 5 names and then drag the rest of the cells down the rows.

What equation would I put in cell C8 in Google Sheets to get this to work?

Thanks!

 

[lrobbo314]

How about this?

thp510
	B	C	D	E
2	Accounts	Assignment		Order
3	1	Kelly		Kelly
4	2	Jon		Jon
5	3	Bob		Bob
6	4	Suzie		Suzie
7	5	Tom		Tom
8	6	Tom
9	7	Suzie
10	8	Bob
11	9	Jon
12	10	Kelly
13	11	Kelly
14	12	Jon
15	13	Bob
16	14	Suzie
17	15	Tom
18	16	Tom
19	17	Suzie
20	18	Bob
21	19	Jon
22	20	Kelly
23	21	Kelly
24	22	Jon
25	23	Bob
26	24	Suzie
27	25	Tom
28	26	Tom
29	27	Suzie
30	28	Bob
31	29	Jon
32	30	Kelly
Sheet2

Cell Formulas
Range	Formula
B3:B32	B3	=SEQUENCE(30)
C3:C32	C3	=INDEX($E$3:$E$7,IF(ISODD(INT(((ROW(C3)-ROW($C$2))-1)/5)+1),MOD((ROW(C3)-ROW($C$2))-1,5)+1,5-MOD((ROW(C3)-ROW($C$2))-1,5)))
Dynamic array formulas.

 

[thp510]

How about this?

thp510
	B	C	D	E
2	Accounts	Assignment		Order
3	1	Kelly		Kelly
4	2	Jon		Jon
5	3	Bob		Bob
6	4	Suzie		Suzie
7	5	Tom		Tom
8	6	Tom
9	7	Suzie
10	8	Bob
11	9	Jon
12	10	Kelly
13	11	Kelly
14	12	Jon
15	13	Bob
16	14	Suzie
17	15	Tom
18	16	Tom
19	17	Suzie
20	18	Bob
21	19	Jon
22	20	Kelly
23	21	Kelly
24	22	Jon
25	23	Bob
26	24	Suzie
27	25	Tom
28	26	Tom
29	27	Suzie
30	28	Bob
31	29	Jon
32	30	Kelly
Sheet2

Cell Formulas
Range	Formula
B3:B32	B3	=SEQUENCE(30)
C3:C32	C3	=INDEX($E$3:$E$7,IF(ISODD(INT(((ROW(C3)-ROW($C$2))-1)/5)+1),MOD((ROW(C3)-ROW($C$2))-1,5)+1,5-MOD((ROW(C3)-ROW($C$2))-1,5)))
Dynamic array formulas.

Thanks, but I'm having problems with placing $C$2. I had to move my rows of data further down so now my header for my columns doesn't start until row 6 (i.e. C6 is where I have the word "Assignment"). How does the equation change? When I changed $C$2 to $C$6 in the equation I got a different person showing up (it was Tom instead of Kelly on cell C7.

 

[lrobbo314]

I'll be away from my computer for a couple of hours so I can't really mess with it.

But, the idea is that the header cell will have the absolute reference and the cell below it will be relative. So, ot looks like it should be $C$6 and then change C3 to C7. The idea is to, for the first cell anyway, to start counting at 1. It's subtraction, so whatever you need to do to return a 1, then as you drag down it will increment up by 1 each time.

 

[thp510]

I'll be away from my computer for a couple of hours so I can't really mess with it.

But, the idea is that the header cell will have the absolute reference and the cell below it will be relative. So, ot looks like it should be $C$6 and then change C3 to C7. The idea is to, for the first cell anyway, to start counting at 1. It's subtraction, so whatever you need to do to return a 1, then as you drag down it will increment up by 1 each time.

Ahh! That make sense. I just redid it your way and it works! Thank you!

 

[lrobbo314]

Awesome!