Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 25 '12 at 17:26

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

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.