Creating Custom Autofill series?

[bluepenguin3]

Hi all,

I have a problem that seems simplistic, but I can't solve it. Maybe I'm using the wrong terms to search. I want to create my own custom way to fill a series. My data looks something like this:

BP0001
BP0001
BP0001
BP0002
BP0002
BP0002
BP0003
BP0003
BP0003

When I drag to autofill, I want to continue this pattern indefinitely. So, the next 3 entries should be BP0004 and then the next three after that BP0005, and onward.

I don't want the series to just continue to repeat the 1, 2, and 3 over and over again, but that's what happens if I select that section and drag to "Fill series".

Is there anyway to get the series to repeat the data 3 times, before moving on to the next sequential number?

Thanks for your time!

 

[Somendra Misra]

Hi,

Try below formula in A1 and drag down.

="BP"&REPT("0",4-LEN(INT((ROWS(A$1:A1)-1)/3)+1))&INT((ROWS(A$1:A1)-1)/3)+1

Regards,

 

[NARAYANK991]

Hi ,

Another formula :

="BP" & TEXT((ROW(A1)+1)/3, "0000")

Enter this in the first cell , where ever that is , and copy down.

Narayan

 

[bosco_yip]

Hi ,

Another formula :

="BP" & TEXT((ROW(A1)+1)/3, "0000")

Enter this in the first cell , where ever that is , and copy down.

Narayan

Sequential number increment fill down.

Maybe the roundup problem, as per testing sheet show, row 1 is the increment number, formula put in row 2 A2 and copy across down.

Narayan's formula only worked in the increment of 3 and 4, the other numbers appeared failure.

Here's the testing sheet attached for reference.

Regards
Bosco

 

[bluepenguin3]

Thanks everyone!

Somendra, your formula works perfectly for me. Thank you so much. I'm a little relieved that there wasn't just some obvious button that I wasn't clicking to make this work.

 

[NARAYANK991]

View attachment 42267

Sequential number increment fill down.

Maybe the roundup problem, as per testing sheet show, row 1 is the increment number, formula put in row 2 A2 and copy across down.

Narayan's formula only worked in the increment of 3 and 4, the other numbers appeared failure.

Here's the testing sheet attached for reference.

Regards
Bosco

Hi ,

The TEXT function does round off internally because the format string does not have any decimal places.

The following will work for 5 and 6 :

=TEXT((ROW(A1) + 2)/6, "0000")

OP has asked for 3 , and what was posted works for 3.

Narayan

 

[John Jairo V]

Hi, to all!

Another option could be:
="BP"&TEXT(1+QUOTIENT(ROWS(A$1:A1)-1,3),"0000")

Or:
="BP"&TEXT(1+INT((ROWS(A$1:A1)-1)/3),"0000")

Blessings!