Excel Formula For Re-arranging Numbers

I have got a bit stuck with trying to make a formula in Excel, and last time I asked a question here I got great help, I have come back again. At the moment I have column A with sets of product id's. There are just under 2000. What I need to try and do is have each number showing in another column repeated 16 times, and then in the next column have the 1st 16 numbers each repeated in sequence in each of the 16 numbers in the previous column, and once those 16 are done, have the next 16 repeated the same over the next 16 sets.

Ie.

1---1---1
2---1---2
3---1---3
4---1---4
5---1---5
6---2---1
7---2---2
8---2---3
9---2---4
10--2---5

etc, but over 16 sets of numbers instead of 5. Is this possible? I am sure it is, but am stuck in a brain freeze at the moment.

Thanks for any help in advance.

-

migrated from stackoverflow.comNov 25 '12 at 17:26

This question came from our site for professional and enthusiast programmers.

It looks like the middle column can be calculated with a formula something like:

``````=QUOTIENT( A1 , max_product_id )
``````

and the right column can be calculated with a formula like:

``````=1+MOD( A1 , max_product_id )
``````

where "max_product_id" is the name of a named cell containing the value 16, and the relative reference "A1" is tweaked to refer to the cell in the same row in the left column. After the top row is set up right, you "pull down" those two cells and the relative reference should automagically do the right thing.

-