Create Teams Snake Draft Style

Welcome to the forum.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT a release number like 2022) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

In A2

=((ROWS($1:1)-1)*4+COLUMN(A$1))

copy across to D

in A3

=((ROWS($1:2)-1)*4+COLUMNS(A$1:$D$1))

copy across to D

select A2:D3 and drag down as far as required

Thank you both for the responses! both do work in the way I wrote it out, but I should have been more specific in my table I meant I'd like to bring back the name of the person in that rank. So maybe a more accurate representation would be this:

Rank	Contestant	Speed	(empty col)	Team A	Team B	Team C	Team D
1	Summer	96		Summer	Lindsey	John	Josh
2	Lindsey	93		8th Place Name	7th Place Name	Chloe	Amy
3	John	84		9th Place Name	10th Place Name	...	...
4	Josh	83
5	Amy	80
6	Chloe	77

That works! Thank you so much!