numbering of rows

F

FreddieP

Hi I would like to identify rows with numbers depending on what text is in
the first column.

Lets say the first 6 rows contains the following

A
A
A
B
B
C
C
C
C
D

Then I would like in the next column to return 1 for A, 2 for B, 3 for C and
so on.
 
M

Mike H

Probably best to have a 2 column table containg the letter in the left column
and the number in the right and your formula could then be:-

=VLOOKUP(A1,J$1:K$26,2,FALSE)

Drag this down as required
 
B

bj

several questions before a suggestion
is the reference letter the only thing in the Cell?
how many variations are there in column A?
If the answers are yes and less than 7, nested Ifs could do what you want
=if(A1="A",1,if(A1="B",2,...))
if the answers are yes and "A bunch"
seting up a lookup table would work
if the answer to the first question is "No"
then the response is that we need more information to give good suggestions,
 
V

Vergel Adriano

Freddie,

Assuming the values you test are limited to letters of the alphabet in
uppercase, in B1 enter the formula:

=CODE(A1)-64

copy down as needed..

You could also use VLOOKUP. Search the Excel help for VLOOKUP to see an
explanation and example..
 
Top