Help with a Formula to turn various words into numbers

[College_Guru]

Hello ExcelBanter Experts,

I'm soliciting help for a project. I have a column row with city name
in them, I'd like to write a conditional formula that will convert thos
names into several different numeric values that we can use to evaluat
them. problem is I have a LOT of cities and I need a great big formul
to do. Any thoughts on how I can set this up?? I'm using Excel 2007
Thanks in advance

 

[College_Guru]

Can you provide an example of some data and an explanation of how yo
assign any given numeric value to a specific city?

Basically we have a column of cities EX.
Seattle
Atlanta
San Francisco
Everett
Portland

What I want to do is have another column display a corresponding numeri
value for that city. These values will correspond to geographi
territories for our systems. Ex. Seattle = 1, Atlanta=3 and so forth

 

[College_Guru]

You're still not explaining particularly well what you're after.

Let me ask it this way, WHY does Seattle =1 and Atlanta = 3 etc.??
Are there set rules to each city/number combination?
Do you have a table with each city and number and you just want to loo
up the relevant one?
Are you just trying to assign a number to a city then move on to th
next number when the city changes?

Without that information, nobody here can assist with your origina
query.

Okay, Seattle = 1 because that's the value we want to assign to tha
region. I need there to be 3 distinct groups of cities, so that when th
query looks in the City field is generates the right number for tha
enrty.

For example. Seattle, Portland, and Centralia are all category 1 cities
San Francisco, Pullman, and Denver are all category 2. I need a formul
that can distinguish what's being entered. I hope that helps

 

[Spencer101]

Okay, Seattle = 1 because that's the value we want to assign to tha
region. I need there to be 3 distinct groups of cities, so that when th
query looks in the City field is generates the right number for tha
enrty.

For example. Seattle, Portland, and Centralia are all category 1 cities
San Francisco, Pullman, and Denver are all category 2. I need a formul
that can distinguish what's being entered. I hope that helps.

If you know what the numeric value should be for each country then mak
a table with City Names in the first column and the relevant numbers i
the second column.

Then use VLOOKUP to add the relevant numbers into your data.

If you need help with that, let me know

 

[Dave O]

Rather than a conditional formula- by which I presume you mean an IF() formula- would a VLOOKUP() work instead? If you insert a column next to your city name, you could maintain a lookup table on a different tab. This would be much easier to maintain than a monstrously long IF() formula.

 

[College_Guru]

Rather than a conditional formula- by which I presume you mean an IF(
formula- would a VLOOKUP() work instead? If you insert a column next t
your city name, you could maintain a lookup table on a different tab
This would be much easier to maintain than a monstrously long IF(
formula.

This could work, only problem is that when I looked VLookup in Excel i
says the table must be in ascending order by default. Is this th
reference table or the city column? If it's the city column then i
won't do as our system automatically sorts entries by another field

 

[lhkittle]

Hello ExcelBanter Experts,



I'm soliciting help for a project. I have a column row with city names

in them, I'd like to write a conditional formula that will convert those

names into several different numeric values that we can use to evaluate

them. problem is I have a LOT of cities and I need a great big formula

to do. Any thoughts on how I can set this up?? I'm using Excel 2007.

Thanks in advance.









--

College_Guru



Hello ExcelBanter Experts,



I'm soliciting help for a project. I have a column row with city names

in them, I'd like to write a conditional formula that will convert those

names into several different numeric values that we can use to evaluate

them. problem is I have a LOT of cities and I need a great big formula

to do. Any thoughts on how I can set this up?? I'm using Excel 2007.

Thanks in advance.









--

College_Guru



Hello ExcelBanter Experts,



I'm soliciting help for a project. I have a column row with city names

in them, I'd like to write a conditional formula that will convert those

names into several different numeric values that we can use to evaluate

them. problem is I have a LOT of cities and I need a great big formula

to do. Any thoughts on how I can set this up?? I'm using Excel 2007.

Thanks in advance.









--

College_Guru



Hello ExcelBanter Experts,



I'm soliciting help for a project. I have a column row with city names

in them, I'd like to write a conditional formula that will convert those

names into several different numeric values that we can use to evaluate

them. problem is I have a LOT of cities and I need a great big formula

to do. Any thoughts on how I can set this up?? I'm using Excel 2007.

Thanks in advance.

 

[Ron Rosenfeld]

This could work, only problem is that when I looked VLookup in Excel it
says the table must be in ascending order by default. >

You didn't read far enough. You need to set range_lookup to false:

....

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

....