MS Excel: Need to autofill a 3 character value where some values are constant and others are not












2















I would like to autofill (if possible - am happy to do another way) a row that has a pattern shown here:
I1A, I1B, I2A, I2B, I3A, I3B, …
In more descriptive terms, the first character is the letter I, the second character contains numbers increasing from 1 on, but each one is repeated twice, and the third character alternates between the letter A and the letter B.
I have no idea how to do this, so I am seeking assistance.
Thanks!










share|improve this question

























  • Is the autofill across columns of a single row, or rows of a single column?

    – fixer1234
    Feb 3 at 4:03
















2















I would like to autofill (if possible - am happy to do another way) a row that has a pattern shown here:
I1A, I1B, I2A, I2B, I3A, I3B, …
In more descriptive terms, the first character is the letter I, the second character contains numbers increasing from 1 on, but each one is repeated twice, and the third character alternates between the letter A and the letter B.
I have no idea how to do this, so I am seeking assistance.
Thanks!










share|improve this question

























  • Is the autofill across columns of a single row, or rows of a single column?

    – fixer1234
    Feb 3 at 4:03














2












2








2








I would like to autofill (if possible - am happy to do another way) a row that has a pattern shown here:
I1A, I1B, I2A, I2B, I3A, I3B, …
In more descriptive terms, the first character is the letter I, the second character contains numbers increasing from 1 on, but each one is repeated twice, and the third character alternates between the letter A and the letter B.
I have no idea how to do this, so I am seeking assistance.
Thanks!










share|improve this question
















I would like to autofill (if possible - am happy to do another way) a row that has a pattern shown here:
I1A, I1B, I2A, I2B, I3A, I3B, …
In more descriptive terms, the first character is the letter I, the second character contains numbers increasing from 1 on, but each one is repeated twice, and the third character alternates between the letter A and the letter B.
I have no idea how to do this, so I am seeking assistance.
Thanks!







microsoft-excel worksheet-function






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 2 at 4:01







Bonnie N P

















asked Feb 2 at 3:41









Bonnie N PBonnie N P

112




112













  • Is the autofill across columns of a single row, or rows of a single column?

    – fixer1234
    Feb 3 at 4:03



















  • Is the autofill across columns of a single row, or rows of a single column?

    – fixer1234
    Feb 3 at 4:03

















Is the autofill across columns of a single row, or rows of a single column?

– fixer1234
Feb 3 at 4:03





Is the autofill across columns of a single row, or rows of a single column?

– fixer1234
Feb 3 at 4:03










3 Answers
3






active

oldest

votes


















3














This method works for a variety of problems.

We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.



First column [="l"] or any constant character

- Just drop in a couple of "l" chars in successive rows.

- Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)

Second column [= 1,1,2,2,3,3,...]

- Drop a numeric 1 in each of the first two data rows

- third row, set a formula to equal the FIRST row plus one

- fourth row, set a formula to equal the SECOND row

- Select the third and fourth row entries ONLY and drag the fill handle down.

Third column [=A,B,A,B...]

- Enter A and B in the first and second data rows, and then again in the third and fourth data rows

- Select all four and drag the fill handle down.

Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]

- concatenate the other three columns

- drag fill down.



There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.



Advantages:

- Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.

- These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.

- This method lives in your head, not in a defined list somewhere.

Drawbacks:

- Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.






share|improve this answer































    1














    Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.



    Alternative 1



    This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.



    If going across: If the first two cells are A1 and B1, this would go in C1:



    ="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)


    If going down: If the first two cells are A1 and A2, this would go in A3:



    ="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)


    This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.



    For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.



    Since the last character repeats every other cell, it just uses the value from two cells earlier.



    Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.



    Alternative 2



    This approach uses some arithmetic tricks to figure out what goes in each cell.



    The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.



    If going across: If you start in column A, this piece of the formula would be:



    ROUND(COLUMN()/2,0)


    If you start in say column C (the 3rd column), the formula would be:



    ROUND((COLUMN()-2)/2,0)


    If going down: If you start in row 1, this piece of the formula would be:



    ROUND(ROW()/2,0)


    If you start in say row 3, the formula would be:



    ROUND((ROW()-2)/2,0)


    I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).



    The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.



    If going across: If you start in an odd column, this piece would be:



    IF(MOD(COLUMN(),2)=0,"B","A")


    If the starting column number is even, it would be:



    IF(MOD(COLUMN(),2)=0,"A","B")


    If going down: If you start in an odd row, this piece would be:



    IF(MOD(ROW(),2)=0,"B","A")


    If the starting row number is even, it would be:



    IF(MOD(ROW(),2)=0,"A","B")


    The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.



    Putting it together gives you:



    If going across:



    ="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")


    If going down:



    ="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")


    Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.






    share|improve this answer


























    • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

      – Rajesh S
      Feb 4 at 10:13



















    1














    First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this



    image1



    now select these 4 cells, and copy down as long as you want your numbers to be generated



    This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between



    Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.



    in the row below where you have entered the formula above, enter ="I"&A1&"B",



    in effect, this will come to the cell next to the blank cell below 1



    The cell B1 will now have I1A (first formula above)
    The cell B2 will now have I1B (second formula above), like this



    image2



    Select B1:B2, copy down till where you had filled 1,2,3,... in first step



    Your B column will have your list.



    image3



    Excel file in which this is done






    share|improve this answer



















    • 1





      Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

      – fixer1234
      Feb 6 at 20:58











    • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

      – VSRawat
      Feb 6 at 21:04






    • 1





      Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

      – fixer1234
      Feb 6 at 21:14






    • 1





      I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

      – fixer1234
      Feb 6 at 21:34






    • 1





      Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

      – fixer1234
      Feb 6 at 21:56











    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401225%2fms-excel-need-to-autofill-a-3-character-value-where-some-values-are-constant-an%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    3














    This method works for a variety of problems.

    We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.



    First column [="l"] or any constant character

    - Just drop in a couple of "l" chars in successive rows.

    - Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)

    Second column [= 1,1,2,2,3,3,...]

    - Drop a numeric 1 in each of the first two data rows

    - third row, set a formula to equal the FIRST row plus one

    - fourth row, set a formula to equal the SECOND row

    - Select the third and fourth row entries ONLY and drag the fill handle down.

    Third column [=A,B,A,B...]

    - Enter A and B in the first and second data rows, and then again in the third and fourth data rows

    - Select all four and drag the fill handle down.

    Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]

    - concatenate the other three columns

    - drag fill down.



    There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.



    Advantages:

    - Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.

    - These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.

    - This method lives in your head, not in a defined list somewhere.

    Drawbacks:

    - Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.






    share|improve this answer




























      3














      This method works for a variety of problems.

      We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.



      First column [="l"] or any constant character

      - Just drop in a couple of "l" chars in successive rows.

      - Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)

      Second column [= 1,1,2,2,3,3,...]

      - Drop a numeric 1 in each of the first two data rows

      - third row, set a formula to equal the FIRST row plus one

      - fourth row, set a formula to equal the SECOND row

      - Select the third and fourth row entries ONLY and drag the fill handle down.

      Third column [=A,B,A,B...]

      - Enter A and B in the first and second data rows, and then again in the third and fourth data rows

      - Select all four and drag the fill handle down.

      Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]

      - concatenate the other three columns

      - drag fill down.



      There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.



      Advantages:

      - Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.

      - These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.

      - This method lives in your head, not in a defined list somewhere.

      Drawbacks:

      - Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.






      share|improve this answer


























        3












        3








        3







        This method works for a variety of problems.

        We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.



        First column [="l"] or any constant character

        - Just drop in a couple of "l" chars in successive rows.

        - Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)

        Second column [= 1,1,2,2,3,3,...]

        - Drop a numeric 1 in each of the first two data rows

        - third row, set a formula to equal the FIRST row plus one

        - fourth row, set a formula to equal the SECOND row

        - Select the third and fourth row entries ONLY and drag the fill handle down.

        Third column [=A,B,A,B...]

        - Enter A and B in the first and second data rows, and then again in the third and fourth data rows

        - Select all four and drag the fill handle down.

        Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]

        - concatenate the other three columns

        - drag fill down.



        There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.



        Advantages:

        - Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.

        - These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.

        - This method lives in your head, not in a defined list somewhere.

        Drawbacks:

        - Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.






        share|improve this answer













        This method works for a variety of problems.

        We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.



        First column [="l"] or any constant character

        - Just drop in a couple of "l" chars in successive rows.

        - Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)

        Second column [= 1,1,2,2,3,3,...]

        - Drop a numeric 1 in each of the first two data rows

        - third row, set a formula to equal the FIRST row plus one

        - fourth row, set a formula to equal the SECOND row

        - Select the third and fourth row entries ONLY and drag the fill handle down.

        Third column [=A,B,A,B...]

        - Enter A and B in the first and second data rows, and then again in the third and fourth data rows

        - Select all four and drag the fill handle down.

        Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]

        - concatenate the other three columns

        - drag fill down.



        There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.



        Advantages:

        - Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.

        - These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.

        - This method lives in your head, not in a defined list somewhere.

        Drawbacks:

        - Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Feb 2 at 6:15









        Haakon DahlHaakon Dahl

        1907




        1907

























            1














            Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.



            Alternative 1



            This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.



            If going across: If the first two cells are A1 and B1, this would go in C1:



            ="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)


            If going down: If the first two cells are A1 and A2, this would go in A3:



            ="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)


            This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.



            For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.



            Since the last character repeats every other cell, it just uses the value from two cells earlier.



            Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.



            Alternative 2



            This approach uses some arithmetic tricks to figure out what goes in each cell.



            The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.



            If going across: If you start in column A, this piece of the formula would be:



            ROUND(COLUMN()/2,0)


            If you start in say column C (the 3rd column), the formula would be:



            ROUND((COLUMN()-2)/2,0)


            If going down: If you start in row 1, this piece of the formula would be:



            ROUND(ROW()/2,0)


            If you start in say row 3, the formula would be:



            ROUND((ROW()-2)/2,0)


            I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).



            The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.



            If going across: If you start in an odd column, this piece would be:



            IF(MOD(COLUMN(),2)=0,"B","A")


            If the starting column number is even, it would be:



            IF(MOD(COLUMN(),2)=0,"A","B")


            If going down: If you start in an odd row, this piece would be:



            IF(MOD(ROW(),2)=0,"B","A")


            If the starting row number is even, it would be:



            IF(MOD(ROW(),2)=0,"A","B")


            The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.



            Putting it together gives you:



            If going across:



            ="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")


            If going down:



            ="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")


            Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.






            share|improve this answer


























            • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

              – Rajesh S
              Feb 4 at 10:13
















            1














            Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.



            Alternative 1



            This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.



            If going across: If the first two cells are A1 and B1, this would go in C1:



            ="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)


            If going down: If the first two cells are A1 and A2, this would go in A3:



            ="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)


            This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.



            For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.



            Since the last character repeats every other cell, it just uses the value from two cells earlier.



            Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.



            Alternative 2



            This approach uses some arithmetic tricks to figure out what goes in each cell.



            The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.



            If going across: If you start in column A, this piece of the formula would be:



            ROUND(COLUMN()/2,0)


            If you start in say column C (the 3rd column), the formula would be:



            ROUND((COLUMN()-2)/2,0)


            If going down: If you start in row 1, this piece of the formula would be:



            ROUND(ROW()/2,0)


            If you start in say row 3, the formula would be:



            ROUND((ROW()-2)/2,0)


            I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).



            The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.



            If going across: If you start in an odd column, this piece would be:



            IF(MOD(COLUMN(),2)=0,"B","A")


            If the starting column number is even, it would be:



            IF(MOD(COLUMN(),2)=0,"A","B")


            If going down: If you start in an odd row, this piece would be:



            IF(MOD(ROW(),2)=0,"B","A")


            If the starting row number is even, it would be:



            IF(MOD(ROW(),2)=0,"A","B")


            The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.



            Putting it together gives you:



            If going across:



            ="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")


            If going down:



            ="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")


            Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.






            share|improve this answer


























            • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

              – Rajesh S
              Feb 4 at 10:13














            1












            1








            1







            Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.



            Alternative 1



            This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.



            If going across: If the first two cells are A1 and B1, this would go in C1:



            ="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)


            If going down: If the first two cells are A1 and A2, this would go in A3:



            ="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)


            This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.



            For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.



            Since the last character repeats every other cell, it just uses the value from two cells earlier.



            Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.



            Alternative 2



            This approach uses some arithmetic tricks to figure out what goes in each cell.



            The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.



            If going across: If you start in column A, this piece of the formula would be:



            ROUND(COLUMN()/2,0)


            If you start in say column C (the 3rd column), the formula would be:



            ROUND((COLUMN()-2)/2,0)


            If going down: If you start in row 1, this piece of the formula would be:



            ROUND(ROW()/2,0)


            If you start in say row 3, the formula would be:



            ROUND((ROW()-2)/2,0)


            I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).



            The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.



            If going across: If you start in an odd column, this piece would be:



            IF(MOD(COLUMN(),2)=0,"B","A")


            If the starting column number is even, it would be:



            IF(MOD(COLUMN(),2)=0,"A","B")


            If going down: If you start in an odd row, this piece would be:



            IF(MOD(ROW(),2)=0,"B","A")


            If the starting row number is even, it would be:



            IF(MOD(ROW(),2)=0,"A","B")


            The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.



            Putting it together gives you:



            If going across:



            ="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")


            If going down:



            ="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")


            Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.






            share|improve this answer















            Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.



            Alternative 1



            This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.



            If going across: If the first two cells are A1 and B1, this would go in C1:



            ="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)


            If going down: If the first two cells are A1 and A2, this would go in A3:



            ="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)


            This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.



            For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.



            Since the last character repeats every other cell, it just uses the value from two cells earlier.



            Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.



            Alternative 2



            This approach uses some arithmetic tricks to figure out what goes in each cell.



            The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.



            If going across: If you start in column A, this piece of the formula would be:



            ROUND(COLUMN()/2,0)


            If you start in say column C (the 3rd column), the formula would be:



            ROUND((COLUMN()-2)/2,0)


            If going down: If you start in row 1, this piece of the formula would be:



            ROUND(ROW()/2,0)


            If you start in say row 3, the formula would be:



            ROUND((ROW()-2)/2,0)


            I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).



            The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.



            If going across: If you start in an odd column, this piece would be:



            IF(MOD(COLUMN(),2)=0,"B","A")


            If the starting column number is even, it would be:



            IF(MOD(COLUMN(),2)=0,"A","B")


            If going down: If you start in an odd row, this piece would be:



            IF(MOD(ROW(),2)=0,"B","A")


            If the starting row number is even, it would be:



            IF(MOD(ROW(),2)=0,"A","B")


            The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.



            Putting it together gives you:



            If going across:



            ="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")


            If going down:



            ="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")


            Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Feb 3 at 4:33

























            answered Feb 2 at 7:41









            fixer1234fixer1234

            18.8k144982




            18.8k144982













            • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

              – Rajesh S
              Feb 4 at 10:13



















            • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

              – Rajesh S
              Feb 4 at 10:13

















            both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

            – Rajesh S
            Feb 4 at 10:13





            both working properly, it looks simple but is bit complicated too. Perfect 10 ☺

            – Rajesh S
            Feb 4 at 10:13











            1














            First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this



            image1



            now select these 4 cells, and copy down as long as you want your numbers to be generated



            This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between



            Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.



            in the row below where you have entered the formula above, enter ="I"&A1&"B",



            in effect, this will come to the cell next to the blank cell below 1



            The cell B1 will now have I1A (first formula above)
            The cell B2 will now have I1B (second formula above), like this



            image2



            Select B1:B2, copy down till where you had filled 1,2,3,... in first step



            Your B column will have your list.



            image3



            Excel file in which this is done






            share|improve this answer



















            • 1





              Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

              – fixer1234
              Feb 6 at 20:58











            • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

              – VSRawat
              Feb 6 at 21:04






            • 1





              Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

              – fixer1234
              Feb 6 at 21:14






            • 1





              I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

              – fixer1234
              Feb 6 at 21:34






            • 1





              Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

              – fixer1234
              Feb 6 at 21:56
















            1














            First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this



            image1



            now select these 4 cells, and copy down as long as you want your numbers to be generated



            This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between



            Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.



            in the row below where you have entered the formula above, enter ="I"&A1&"B",



            in effect, this will come to the cell next to the blank cell below 1



            The cell B1 will now have I1A (first formula above)
            The cell B2 will now have I1B (second formula above), like this



            image2



            Select B1:B2, copy down till where you had filled 1,2,3,... in first step



            Your B column will have your list.



            image3



            Excel file in which this is done






            share|improve this answer



















            • 1





              Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

              – fixer1234
              Feb 6 at 20:58











            • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

              – VSRawat
              Feb 6 at 21:04






            • 1





              Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

              – fixer1234
              Feb 6 at 21:14






            • 1





              I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

              – fixer1234
              Feb 6 at 21:34






            • 1





              Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

              – fixer1234
              Feb 6 at 21:56














            1












            1








            1







            First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this



            image1



            now select these 4 cells, and copy down as long as you want your numbers to be generated



            This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between



            Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.



            in the row below where you have entered the formula above, enter ="I"&A1&"B",



            in effect, this will come to the cell next to the blank cell below 1



            The cell B1 will now have I1A (first formula above)
            The cell B2 will now have I1B (second formula above), like this



            image2



            Select B1:B2, copy down till where you had filled 1,2,3,... in first step



            Your B column will have your list.



            image3



            Excel file in which this is done






            share|improve this answer













            First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this



            image1



            now select these 4 cells, and copy down as long as you want your numbers to be generated



            This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between



            Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.



            in the row below where you have entered the formula above, enter ="I"&A1&"B",



            in effect, this will come to the cell next to the blank cell below 1



            The cell B1 will now have I1A (first formula above)
            The cell B2 will now have I1B (second formula above), like this



            image2



            Select B1:B2, copy down till where you had filled 1,2,3,... in first step



            Your B column will have your list.



            image3



            Excel file in which this is done







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Feb 6 at 17:48









            VSRawatVSRawat

            15512




            15512








            • 1





              Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

              – fixer1234
              Feb 6 at 20:58











            • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

              – VSRawat
              Feb 6 at 21:04






            • 1





              Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

              – fixer1234
              Feb 6 at 21:14






            • 1





              I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

              – fixer1234
              Feb 6 at 21:34






            • 1





              Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

              – fixer1234
              Feb 6 at 21:56














            • 1





              Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

              – fixer1234
              Feb 6 at 20:58











            • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

              – VSRawat
              Feb 6 at 21:04






            • 1





              Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

              – fixer1234
              Feb 6 at 21:14






            • 1





              I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

              – fixer1234
              Feb 6 at 21:34






            • 1





              Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

              – fixer1234
              Feb 6 at 21:56








            1




            1





            Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

            – fixer1234
            Feb 6 at 20:58





            Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.

            – fixer1234
            Feb 6 at 20:58













            O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

            – VSRawat
            Feb 6 at 21:04





            O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.

            – VSRawat
            Feb 6 at 21:04




            1




            1





            Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

            – fixer1234
            Feb 6 at 21:14





            Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)

            – fixer1234
            Feb 6 at 21:14




            1




            1





            I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

            – fixer1234
            Feb 6 at 21:34





            I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)

            – fixer1234
            Feb 6 at 21:34




            1




            1





            Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

            – fixer1234
            Feb 6 at 21:56





            Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.

            – fixer1234
            Feb 6 at 21:56


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401225%2fms-excel-need-to-autofill-a-3-character-value-where-some-values-are-constant-an%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Probability when a professor distributes a quiz and homework assignment to a class of n students.

            Aardman Animations

            Are they similar matrix