+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,824

    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. #3
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

    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. #4
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

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

    Quote Originally Posted by josh101287 View Post
    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. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,824

    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

    By the way, if that answers your initial query then please mark the thread as SOLVED, using Thread Tools above your first post.
    Last edited by Pete_UK; 01-26-2016 at 09:00 AM.

  6. #6
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

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

    Excellent explanation Pete!

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,824

    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. #8
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

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

    Quote Originally Posted by pete_uk View Post
    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. #9
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

    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. #10
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

    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. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,824

    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. #12
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

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

    yes that is correct

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,824

    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. #14
    Registered User
    Join Date
    05-03-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    51

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

    thanks it helps tremendously

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Check if formula is autofilled
    By kimrh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2015, 06:23 AM
  2. [SOLVED] Code for dragging down a formula across the row below the last line of data
    By ach5170 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2013, 09:29 AM
  3. [SOLVED] Auto create a formula to apply to the whole column without dragging
    By teenyjem in forum Excel General
    Replies: 4
    Last Post: 08-07-2012, 06:08 AM
  4. averageifs formula doesnt change when autofilled
    By jacobhandson in forum Excel General
    Replies: 2
    Last Post: 01-29-2012, 09:32 PM
  5. Excel 2007 : changing the autofilled am/pm for times?
    By cobandrob in forum Excel General
    Replies: 0
    Last Post: 10-04-2010, 03:13 PM
  6. Dragging Formula Code
    By RynNap1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2009, 11:45 AM
  7. formula autofilled question
    By DataGuy78 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2007, 01:17 AM

Bookmarks

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