# formula to create a code that will be autofilled by dragging down

1. ## formula to create a code that will be autofilled by dragging down

i am trying to create a formula that i can jus drag down and auto fill all the cells that has certain character ranges at certain points in the code. if that makes sense.

for example:
the first code is A01A1
second is A01A2

the ranges I'm looking for are:
for the first part where A is range: A-Z
second part where 01 is range: 01-99
third part where A is range:A-Z
last part where 1 is rang 1-9

im not sure if this is possible but any help would be great thanks!

2. ## Re: formula to create a code that will be autofilled by dragging down

You can put this formula in your first cell:

=CHAR(MOD(INT((ROWS(\$1:1)-1)/9/26/99),26)+65) & TEXT(MOD(INT((ROWS(\$1:1)-1)/9/26),99)+1,"00") & CHAR(MOD(INT((ROWS(\$1:1)-1)/9),26)+65) & MOD(ROWS(\$1:1)-1,9)+1

and then copy down as required. I tested it to cell A20000 (from A1) and the value at that point was A86M2.

The codes will start to repeat at just over 600,000 rows (i.e. 26*99*26*9).

Hope this helps.

Pete

3. ## Re: formula to create a code that will be autofilled by dragging down

awesome thats a life saver!!! Thanks so much. Could you explain the formula so i can understand how it works?

4. ## Re: formula to create a code that will be autofilled by dragging down

Originally Posted by josh101287
awesome thats a life saver!!! Thanks so much. Could you explain the formula so i can understand how it works?
Break formula at & and add each part in separate cell & drag down to de-code it

5. ## Re: formula to create a code that will be autofilled by dragging down

Thank you both for the reps.

@Josh: the formula is in 4 parts, each separated by an ampersand ( & ), and I've put spaces around that to make it clearer. As excelliot points out, you can put each element in a separate cell to check the effect they produce. The formula relies on the ROWS(\$1:1) term, which initially returns 1, but when the formula is copied down that term produces a sequential number, 2, 3, 4, etc. on successive rows - the trick is to convert that number into a different sequence and one of the simplest ways is to use MOD, as can be seen in the final term, i.e.:

MOD(ROWS(\$1:1)-1,9)+1

MOD gives the remainder after division, so in this case 1 is subtracted from the ROWS term and then is divided by 9, which will result in the sequence 0,1,2,3,4,5,6,7,8,0,1,2,3, etc. (remember that these are the remainders after division), so the 1 is added back on to give the sequence 1 to 9 which then repeats itself.

Another way of converting the basic sequence is to use the INT function, as in the second least significant term:

INT((ROWS(\$1:1)-1)/9)

Again, 1 is subtracted from the ROWS term and then this is divided by 9 and we take the whole number part, so this results in the sequence 0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,2,2,2,2 and so on, i.e. it produces 9 zeros, then 9 1's, then 9 2's and it would continue doing this down the column. However, we only want this to go up to 25 and then to wrap back to zero, so that INT term is itself put inside a MOD term with a divisor of 26 (letters of the alphabet), and then by adding 65 onto that and putting it into a CHAR term, it is converted into a letter sequence, where A is repeated 9 times, then B for 9 times etc.

As we move to the more significant parts, so we have to divide by the number of combinations in the lower parts, so the second term has a divisor in the INT function of 9 and 26, with the MOD function having a divisor of 99, and the first term has a divisor of 9, 26 and 99 in the INT function and a divisor of 26 in the MOD function. The second term needs to return a 2-digit number, so it is placed within a TEXT function to ensure this, and the first term is surrounded by a CHAR function like the third term.

Hope this helps.

Pete

6. ## Re: formula to create a code that will be autofilled by dragging down

Excellent explanation Pete!

7. ## Re: formula to create a code that will be autofilled by dragging down

Thanks for the comment. I like to think that the forum is an educational setting, i.e. not just giving a correct formula to solve a particular problem.

Pete

8. ## Re: formula to create a code that will be autofilled by dragging down

Originally Posted by pete_uk
thanks for the comment. I like to think that the forum is an educational setting, i.e. Not just giving a correct formula to solve a particular problem.

Pete

very true

9. ## Re: formula to create a code that will be autofilled by dragging down

thanks! yea I try to learn from and understand the information i receive from this forum to.

10. ## Re: formula to create a code that will be autofilled by dragging down

This is solved but i was wondering if i could take one step further and in the second part be able to have it where i have it down one column as odd numbers so A01A1, A03A1, A05A1, etc and then evens down another so A02A1, A04A1, A06A1, etc

11. ## Re: formula to create a code that will be autofilled by dragging down

So it is the second and third digits that you want to be even or odd, but for the last digit to remain as it was (i.e. 1 to 9)?

Pete

12. ## Re: formula to create a code that will be autofilled by dragging down

yes that is correct

13. ## Re: formula to create a code that will be autofilled by dragging down

OK, the formula to use for odd numbers (i.e. from 01 up to 99) is this:

=CHAR(MOD(INT((ROWS(\$1:1)-1)/9/26/50),26)+65) & TEXT(MOD(INT((ROWS(\$1:1)-1)/9/26),50)*2+1,"00") & CHAR(MOD(INT((ROWS(\$1:1)-1)/9),26)+65) & MOD(ROWS(\$1:1)-1,9)+1

this will change from A99Z9 to B01A1 at row 11701 if you start with the formula in row 1, as there are 50 odd numbers in the range from 01 to 99 inclusive.

The formula to use for even numbers is:

=CHAR(MOD(INT((ROWS(\$1:1)-1)/9/26/49),26)+65) & TEXT((MOD(INT((ROWS(\$1:1)-1)/9/26),49)+1)*2,"00") & CHAR(MOD(INT((ROWS(\$1:1)-1)/9),26)+65) & MOD(ROWS(\$1:1)-1,9)+1

and this will change from A98Z9 to B02A1 on row 11467 if you start in row 1, as there are only 49 even numbers in the range from 01 to 99 (i.e. 00 isn't included).

Hope this helps.

Pete

14. ## Re: formula to create a code that will be autofilled by dragging down

thanks it helps tremendously

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1