Hi all. I have a lot of data in columns in text format that I want to
code in to whole numbers.

eg. a lot of rows in say column A than contain either "red", "blue",
"black" etc.

I need a formula (ideally able to placed on a different sheet!) for
corresponding rows in a new column eg: C, that would say: if A1 "red"
then C1=0, if A1="blue" then C1=2, if A1="black" then C1=3 and so
forth with up to 20 arguments!.

Is this at all possiable.

Thanks. Vince

Vinnie,
Yes it is and it uses vlookup.

Make a table on a sheet with the code table
[A] [b]
red 0
blue 2
black 3
..... and so

now on your new sheet in the first row what you wnat the information enter
(here i assume A1 on sheet1 with the lookup table on sheet2 in columns a and
b.

=vlookup('sheet1'!A1,'sheet2'!A:B,2,false)
and then copy it down the column.

'sheet1'!A1 = the lookup value
'sheet2'!A:B = the lookup table
2 = the answer lookup is in the second column
false = find an exact match

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"vinnie123" wrote:

Hi all. I have a lot of data in columns in text format that I want to
code in to whole numbers.

eg. a lot of rows in say column A than contain either "red", "blue",
"black" etc.

I need a formula (ideally able to placed on a different sheet!) for
corresponding rows in a new column eg: C, that would say: if A1 "red"
then C1=0, if A1="blue" then C1=2, if A1="black" then C1=3 and so
forth with up to 20 arguments!.

Is this at all possiable.

Thanks. Vince