I have three colms that hold Rank, State Name, and State Number (Colms L-N rows 2-51). I also have a count (ColmA row5) that sets up (max of 10) colms Rooms A-J (starts in Colm B and goes through Colm K). The formula in A is (roundup count of colm L/a given number of teams they want in a room (variable)) The number of colms will vary depending on the number of teams they want in each of the rooms, but can not have more than 10 rooms. Here is what I need to do. I need Rank=1 to put the State Name in Colm B2 and Rank=2 state name goes into colm C2 through however many colms and then start with B3, etc.

IE: IF I have three rooms: (Rank 2 = Al., Rank 3 = NY, Rank 1 = NJ, Rank 4 = TX, Rank 5 = ND)

B2 = NJ C2 = AL D2 = NY
B3 = TX C3 = ND etc.......
etc...... etc..... etc......

I thank you in advance for any assistance. Oh, and I only have basic knowledge of excel so please try and keep as simple as possible.