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

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!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Excel file in which this is done