Thanks you sooo much for your time. I will try this today and post back.

"Sandy Mann" wrote:

I set all the letters down Column G and the corresponding codes in Column H.
Then, assuming that the letters are in Column A by themselves, and do not
have leading/trailing spaces I used the formula:

=VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code.

If the letters do/could have spaces around them then try:

=VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE)

If Services/fees are in the same cell as the letters then, (assuming that
there is no leading space), try:

=VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE)

If the letters and Services/fees are in the same cell with a leading space
then try:

=VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALS E)

Note that the range down to row 133 is much bigger then the codes will need
so that when your manager invents more codes you can just addthen a the
bottom of all the other codes.

Also the codes do not have to be in any order, the formula will still find
them.

If none of the above work then post back.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"dp" wrote in message
...
My manager has identified services/fees into 7 categories. No logic that
I
can see! !Expanding on my original example AB, C, EA, EM, HT ... all =
number
2. In fact there are 12 letter combinations that = 2.

I will only receive this report quarterly so I could continue to add the
numbers manually. Thanks

"Sandy Mann" wrote:

What is the logic behind how the 1 - 7 codes are entered?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"dp" wrote in message
...
Thanks for your reply.
Tried what you said but the problem I have is that all the "A's" are
not
#1.
A, B = #1
AB, C = #2
Thanks again

"Sherin" wrote:


A simple but practical solution would be to:-

a. Type 1 and 2 in first two rows
b. Block the above two cells
c. Double click on the small box appearing in the right hand corner in
the
bottom part of the selection.

Next option would be to:-

a. Type 1 in the first row (say in A2)
b. Type the formula A2+1 in the next row (i.e. in A3)
c. Copy the cell A3
d. Block and select the remaining cells and paste

Hope it helps.

Sherin
"dp" wrote:

I receive a report with a 1 or 2 alpha code/description in col A.,
such
as:
A SERVICE
AB SERVICE
AL FEE
AM SERVICE
B SERVICE
C SERVICE
DI FEE

I insert a column and manually type a single digit code (1 to 7) so
I
can
use Sort by that digit, and use Data, Subtotal.
1 A SERVICE
2 AB SERVICE
6 AL FEE
6 AM SERVICE
1 B SERVICE
2 C SERVICE
5 DI FEE

Is there a way to eliminate the manual typing of the numbers 1-7?
There are
about 70 rows. I am Using excel 2003, Windows 2000 Professional

Hope I made this clear. Just learning. TIA