Assigning a sequential number for a certain counts

0

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

Answer
Discuss

Answers

0
Selected Answer

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)+1

To 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)+1

Finally, 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.

Discuss

Discussion

You are a Life Saver!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ledezmacharlie (rep: 2) Oct 27, '18 at 2:34 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login