Autofill with alpha?

[tinkythomas]

Hi,

Is it possible to autofill with an alpha instead of a number? I would like to fill the value in col E and restart if the value in col B changes as shown below.....

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 79px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"><COL style="WIDTH: 78px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">EIACODXA</TD><TD style="FONT-WEIGHT: bold">LSACONXB</TD><TD style="FONT-WEIGHT: bold">ALTLCNXB</TD><TD style="FONT-WEIGHT: bold">LCNTYPXB</TD><TD style="FONT-WEIGHT: bold">TASKCDCA</TD><TD style="FONT-WEIGHT: bold">TSKFRQCA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAC</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAD</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAE</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAF</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAG</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAC</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAD</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAE</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAF</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAG</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAH</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>PLISN71</TD><TD>E3112</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAI</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>PLISN71</TD><TD>E3113</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>PLISN71</TD><TD>E3113</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>PLISN71</TD><TD>E3113</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAC</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>PLISN71</TD><TD>E3113</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAD</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>PLISN71</TD><TD>E3113</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAE</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

If the value in col B doesn't change then fill to Z then restart over at BA as shown below.....

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 79px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"><COL style="WIDTH: 78px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">EIACODXA</TD><TD style="FONT-WEIGHT: bold">LSACONXB</TD><TD style="FONT-WEIGHT: bold">ALTLCNXB</TD><TD style="FONT-WEIGHT: bold">LCNTYPXB</TD><TD style="FONT-WEIGHT: bold">TASKCDCA</TD><TD style="FONT-WEIGHT: bold">TSKFRQCA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAC</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAD</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAE</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAF</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAG</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAI</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAJ</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAK</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAL</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAM</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAN</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAO</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAP</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAQ</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAR</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAS</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAT</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAU</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAV</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAW</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAX</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAY</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEAZ</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEBA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEBB </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEBC</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD>PLISN71</TD><TD>E3111</TD><TD style="TEXT-ALIGN: right">0</TD><TD>P</TD><TD>JGOOEBD</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

When BZ is reached and the value in col B hasn't changed then start over at C (JGOOECA etc).

Would this be possible without vba? Thank-you

 

[pgc01]

Hi

Try in E2:

="JOGOE"&CHAR(CODE("A")+INT(COUNTIF($B$1:B1,B2)/26))&CHAR(CODE("A")+MOD(COUNTIF($B$1:B1,B2),26))

Copy down

 

[tinkythomas]

Hi

Try in E2:

="JOGOE"&CHAR(CODE("A")+INT(COUNTIF($B$1:B1,B2)/26))&CHAR(CODE("A")+MOD(COUNTIF($B$1:B1,B2),26))

Copy down

Thank you so much, that works perfectly. Although I don't pretend to understand how?

I would be greatful if you could explain how this formula works.

Thanks again, it's appreciated.

 

[pgc01]

Hi again

I'm glad it helped.

The letters have codes that are sequencial.

Code("B")=Code("A")+1
Code("C")=Code("A")+2
etc.

So when I use in the formula

Char(Code("A")+Number),

I just need to calculate the correct number. I calculate the number simply by counting how many times the value in column B has already appeared.

For example in the first table you posted, in row 14 the value in column B, E3111, has already appeared 5 times before.

Char(Code("A")+5) gives me the letter to append in row 14, in this case the "F".

HTH

 

[tinkythomas]

Thank you for taking the time to explain. With your explanation along with evaluating the formula I now understand, very clever!

Thanks for your help.