How To Create Repeating Serial Numbers With Excel Formulas

Katarakyat.id

Excel number looping formula to create repeating serial numbers or repeating sequence numbers 1,1,1,2,2,2,3,3,3 and 1,2,3,1,2,3,1,2,3 in microsoft excel.

How To Create A Repeating Number / Number Series With Excel Formulas

Looping Formulas In Excel – A few days ago I got an excel case that for its solution requires an excel formula that can be used to compose or create repeated serial numbers or sequential numbers that repeat.

The serial numbers I mean here are as follows : 1,2,3,1,2,3,1,2,3, etc. and 1,1,1,2,2,2,3,3,3, etc. for each different excel cell.

For the first model, the repeated serial numbers 1,2,3,1,2,3,1,2,3, etc. actually, I have discussed a little in one part of the tutorial about 6 excel formulas to create automatic ordinal numbers. Please go to that page if you are lost to this blog to get the formula.

As for the second model,namely : 1,1,1,2,2,2,3,3,3, etc. actually we can make with combined IF function and COUNTIF function as follows:

=IF(COUNTIF($A$1:A1;A1)=3;A1+1;A1)

The excel formula above is written in cell A2 and assuming that in cell A1 we manually write the number 1.

It’s just that the formula cannot be applied to be part of another excel function/formula, while my need is to use the repeated numbers in an excel function to compile another excel formula.

So what’s the solution?

The discussion this time will be divided into two parts. The first section discusses excel formulas for compiling the first model repeat series (1,2,3,1,2,3,1,2,3,etc.).), while the second discussion for the second repeating number model (1,1,1,2,2,2,3,3,3,etc.).

Repeated sequence number of Model 1: 1,2,3,1,2,3,1,2,3, etc.

To create the serial number of this first model the formula that can be used is:

=MOD(ROW(A1)-1;3)+1

Then drag it using autofill or copy paste to other cells below it. The result looks like the following picture example:

Repeated Sequence Numbers

If you need the repeated serial numbers arranged to the right side then replace the ROW function in the excel formula with the COLUMN function so that the Excel formula will be as follows:

=MOD(COLUMN(A1)-1;3)+1

The result will look like the following picture:

Repeated Sequence Numbers

To change the maximum number sequence generated replace number 3 in both excel formulas above with the maximum number you want.

The MOD function itself is used to get the remainder of the division of a number. More details you can learn on the following page:

Excel MOD function: how to calculate remainder of division in excel

Repeated sequence number of Model 2: 1,1,1,2,2,2,3,3,3, etc.

For repeated sequence numbers with this second model the excel formula that can be used is:

=INT((ROW(A1)-1)/3)+1

Next it remains to pull down. The result looks like the following picture:

Repeated Serial Numbers

For the use of repeated series in the direction of the column (right side) excel formulas are used :

=INT((COLUMN(A1)-1)/3)+1

The result of the excel formula above is as beirkut:

Repeated Serial Numbers

The workings of the two formulas above are actually very simple, namely utilizing ROW and COLUMN functions commonly used to find out the row number or column number on an excel sheet.

Then perform rounding up to the result of dividing the row or column number by the number of repetitions for each number series. So that the INT function in both formulas you can also replace with other similar functions. For example, functions ROUND, ROUNDUP, TRUNC and others sebaginya.

Discussion about how to round numbers in microsoft excel you can learn on the following page:

How to round numbers behind commas in Excel.

To increase the number of repetitions for each number you just change the number 3 in the excel formula above to the number of repetitions you want.

I am sufficient first for the discussion of the tutorial on how to make this number or repeated numbers. For your benefit, I give it back to each of you.

Hopefully useful and do not forget to share so that other friends also get the benefits.

Thanks for visit my blog!

Related Posts

Leave a Reply

Your email address will not be published.

Close
Minimize
Page:
...
/
0
Please Wait
...
Second
Code: