200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Need Help. We are consistiently having to ship and prepare manifest on excel. Coulmn A would have long listing of values. Columb B is where I am looking to have a sequental number assigned per a certain number count of coulmn a.
Example for every 10 values from a new box number(B) is assigned
The rows of an Excel sheet are already numbered sequentially. The function ROW() returns the number of the row in which it is called. You can manipulate this number to show the number 1 in row 2. =ROW()-1 and copy down.
In order to create a sequential number that increases every 10 rows you can use the INT() function. INT() returns the an integer calculated from an expression supplied as argument. For example, INT(9/10) = 0 but INT(10/10) returns 1, INT(19/10) = 1 etc.
The basic formula below returns 0 in rows 1 to 10, 1 in rows 11 to 20 etc.
=INT(ROW()/10)To start counting from 1 modify the result.
=INT(ROW()/10)+1To place the 1 in a row other than sheet row 1 deduct the row number in which you want to have the 1 from the row number. The formula below will place the number 1 in row 2.
=INT((Row()-2)/10)+1Finally, to omit repeating numbers, use the MOD() function. It returns the unused remainder of a divison, the modulus. MOD(9,10) = 9 and MOD(10,10) = 0. The formula below returns a blank cell whenever the modulus isn't zero.
=IF(MOD(ROW()-2,10),"",INT((ROW()-2)/10)+1)Note the peculiarity here: IF(MOD(ROW()-2,10), [True], [False]). This expression is equivalent to IF(MOD(ROW()-2,10)<>0, [True], [False]). The <>0 can be omitted.