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!
3 Answers
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 reusable 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.
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 1less 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.

both working properly, it looks simple but is bit complicated too. Perfect 10 ☺ Feb 4, 2019 at 10:13
First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this
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
Select B1:B2, copy down till where you had filled 1,2,3,... in first step
Your B column will have your list.

1Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets. Feb 6, 2019 at 20:58

O.P. has tagged microsoftexcel 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.– VSRawatFeb 6, 2019 at 21:04

1Answers 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. :) Feb 6, 2019 at 21:14

1I 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. :) Feb 6, 2019 at 21:34

1Not on this question, but I've done that on others. Google Sheets is offtopic; 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. Feb 6, 2019 at 21:56