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.

Can you provide an example of some data and an explanation of how you 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 numeric value for that city. These values will correspond to geographic territories for our systems. Ex. Seattle = 1, Atlanta=3 and so forth.

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 look up the relevant one?
Are you just trying to assign a number to a city then move on to the next number when the city changes?

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

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

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

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

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

If you need help with that, let me know.

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.

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

On Wednesday, July 25, 2012 3:36:13 PM UTC-7, College_Guru wrote:
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



On Wednesday, July 25, 2012 3:36:13 PM UTC-7, College_Guru wrote:
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



On Wednesday, July 25, 2012 3:36:13 PM UTC-7, College_Guru wrote:
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



On Wednesday, July 25, 2012 3:36:13 PM UTC-7, College_Guru wrote:
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

On Tue, 31 Jul 2012 00:43:18 +0000, College_Guru wrote:

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.

....