Formula for algorithm on excel

Question

I have this table:

Queue¦  Room 1¦ Room 2¦  Room 3¦    Room 4¦ Room 5¦     Room 6¦ 
1    ¦    1   ¦    0  ¦    0
2    ¦    2   ¦    0  ¦    0
3    ¦    3   ¦    0  ¦    0
4    ¦    4   ¦    0  ¦    0
5    ¦    5   ¦    0  ¦    0
6    ¦    6   ¦    0  ¦    0
7    ¦    7   ¦    0  ¦    0
8    ¦    8   ¦    0  ¦    0
9    ¦    9   ¦    0  ¦    0
10   ¦    10  ¦    0  ¦    0
11   ¦    11  ¦    0  ¦    0
12   ¦    12  ¦    0  ¦    0
13   ¦    12  ¦    1  ¦    0
14   ¦    12  ¦    2  ¦    0
15   ¦    12  ¦    3  ¦    0
...  ¦    ..  ¦    .. ¦    ..
36   ¦    12  ¦    12 ¦    1
37   ¦    12  ¦    12 ¦    2
38   ¦    12  ¦    12 ¦    3
...  ¦    ..  ¦    .. ¦    ..

To give some background I am trying to create essentially a formula that can look at the "Queue" column and place those in the "Queue" to "Room 1" and keep placing them in "Room 1" until it is full (it's full when it reaches 12) I then want it to place those in the "Queue" to "Room 2", so when it hits 13 (in the Queue column) and onward they are placed in "Room 2", until it's full at 12 places again, pushing on to "Room 3" and so on so on.

I'm new to excel and I'm not to sure if I am going about this the right way. the formula I have so far:

=OFFSET(A2, 0, $A$2)

This basically looks at "Queue", then returns the number next the to cell it's looking at. I can populate the fields from "Room 3" onward with this formula, but i am wondering is there is a better way to do this? I was also thinking of using an IFstatement but i'm not sure how to increment the IFstatement such as below:

=IF(a13=12,"1","0")

but how would I increment "1" to make it "2", then to make it "3" in the formula, until it reaches 12 again and starts on the next row?

after 12 in the same column I would want the rows below it to say 12 as well.

Apologies if I have confused anyone but I am finding it tricky to search for help online as I don't know how to describe the formula I am looking for.

ALSO: Is there a better title for my query to help others in a similar challenge to mines, find this question?

Answer 1

Thanks for this.

this sort of works however doesnt fill in everything i need. I finally came up with this formula that works. posting here for anyone else in a similar situation.

=IF(IF($A2-12*(COLUMN()-2)>12,12,$A2-12*(COLUMN()-2))<0,0,IF($A2-12*(COLUMN()-2)>12,12,$A2-12*(COLUMN()-2)))

Answer 2

I have assumed that your table starts in A1.

In B2, enter the formula

=IF(OR(ROW()<(COLUMN()-2)*12+2,ROW()>(COLUMN()-1)*12+1),"",INDEX($A:$A,ROW()))

and copy down to match your queue and then across to match your rooms.

And alternative would be to just use one column of formulas.  Enter this into cell B2:

="Room " & INT((ROW()-2)/12)+1

and copy down to match your queue.