software

I keep an extensive list of contacts in Excel. The columns a
FirstName, LastName, Address, City, State, Zip, County

In our sales department, they have the States broken up into regions. How
can I have excel automatically assign the region number in my list in it's
own column based on the state and and county columns?

I am trying to avoid having to go through a contact list of 500 or more and
manually putting in the region number into a column next to the County.

Can anyone help?

Hi!

You need to create (if there isn't one already) a table that lists the
regions and their corresponding state/counties.

The you'd use some type of lookup formula.

Can't suggest anything more specific without more details.

Biff

quot;sskirvinquot; gt; wrote in message
...
gt;I keep an extensive list of contacts in Excel. The columns a
gt; FirstName, LastName, Address, City, State, Zip, County
gt;
gt; In our sales department, they have the States broken up into regions. How
gt; can I have excel automatically assign the region number in my list in it's
gt; own column based on the state and and county columns?
gt;
gt; I am trying to avoid having to go through a contact list of 500 or more
gt; and
gt; manually putting in the region number into a column next to the County.
gt;
gt; Can anyone help?
Extending Biff's comments: set up a 2-column table with the states in the
left column amp; the region in the right, like so:

FL Southeast
CA Southwest
WA Northwest

Name the table Regions

for each row of contacts use a formula like this, assuming state is in
column E

=vlookup(e2,region,2,0)quot;sskirvinquot; wrote:

gt; I keep an extensive list of contacts in Excel. The columns a
gt; FirstName, LastName, Address, City, State, Zip, County
gt;
gt; In our sales department, they have the States broken up into regions. How
gt; can I have excel automatically assign the region number in my list in it's
gt; own column based on the state and and county columns?
gt;
gt; I am trying to avoid having to go through a contact list of 500 or more and
gt; manually putting in the region number into a column next to the County.
gt;
gt; Can anyone help?

Duke:

This answer was everything I needed. Thank You. My Regions are based on
County and State in 2 separate columns which means I will assign a region
based on the county and state.

I am going to assume that if I create a table that lists the regions on
another sheet in a workbook, the formula will have to look at both state
column and the county column then lookup the region from the named list.

Thank you for the prompt reply. I will be able to figure it out hopefully.

quot;Duke Careyquot; wrote:

gt; Extending Biff's comments: set up a 2-column table with the states in the
gt; left column amp; the region in the right, like so:
gt;
gt; FL Southeast
gt; CA Southwest
gt; WA Northwest
gt;
gt; Name the table Regions
gt;
gt; for each row of contacts use a formula like this, assuming state is in
gt; column E
gt;
gt; =vlookup(e2,region,2,0)
gt;
gt;
gt; quot;sskirvinquot; wrote:
gt;
gt; gt; I keep an extensive list of contacts in Excel. The columns a
gt; gt; FirstName, LastName, Address, City, State, Zip, County
gt; gt;
gt; gt; In our sales department, they have the States broken up into regions. How
gt; gt; can I have excel automatically assign the region number in my list in it's
gt; gt; own column based on the state and and county columns?
gt; gt;
gt; gt; I am trying to avoid having to go through a contact list of 500 or more and
gt; gt; manually putting in the region number into a column next to the County.
gt; gt;
gt; gt; Can anyone help?