# put 1 in first 7 rows, put 2 in next seven rows, and so on

1. ## put 1 in first 7 rows, put 2 in next seven rows, and so on

Is there a less manual way to do this? I have 500 rows of data. I need to put the value "1" in the first 7 rows. I then need to put "2" in the second 7 rows, "3" in the next 7 rows, "4" and so on and so forth.

2. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

I think this formula will do the trick. Fill down through your rows:

Formula:
`Please Login or Register  to view this content.`

3. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

In A1

=INT((ROW()-1)/7+1)

copy down

4. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

in A1

=ROUNDUP(ROW()/7,0)

copy down

5. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

Seems like all formulas suggested will work if the start in cell A1

This should work in any cell

Formula:
`Please Login or Register  to view this content.`

6. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

or row and number independent? something like:
=IF(COUNTIF(\$A\$1:A1,A1)=7,A1+1,A1)
(currently assuming A1 has a number in it, and is the first in the sequence of 7)

A little less overhead if the range is restricted, but put in cell A8 with numbers in up to A7 here;
=IF(COUNTIF(A1:A7,A7)=7,A7+1,A7)

And probably a good idea in all these cases to convert the formulas to values afterwards if they're not going to change.

7. ## Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

Cantosh- that worked perfectly!

There were a few others that worked as well. I wasn't expecting so many responses. Very helpful! Thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1