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.
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.
I think this formula will do the trick. Fill down through your rows:
Formula:Please Login or Register to view this content.
In A1
=INT((ROW()-1)/7+1)
copy down
in A1
=ROUNDUP(ROW()/7,0)
copy down
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.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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.
Last edited by Googlyhead; 03-02-2016 at 12:51 PM.
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)
Bookmarks