How to have Excel Fill In Data in Column based on another columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sskirvin - 31 Mar 2006 04:49 GMT

I keep an extensive list of contacts in Excel. The columns are:
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?

Reply to this Message

Biff - 31 Mar 2006 05:46 GMT

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

>I keep an extensive list of contacts in Excel. The columns are:
> FirstName, LastName, Address, City, State, Zip, County
[quoted text clipped - 8 lines]
>
> Can anyone help?

Reply to this Message

Duke Carey - 31 Mar 2006 14:01 GMT

Extending Biff's comments: set up a 2-column table with the states in the
left column & 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)

> I keep an extensive list of contacts in Excel. The columns are:
> FirstName, LastName, Address, City, State, Zip, County
[quoted text clipped - 7 lines]
>
> Can anyone help?

Reply to this Message

sskirvin - 31 Mar 2006 15:14 GMT

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.

> Extending Biff's comments: set up a 2-column table with the states in the
> left column & the region in the right, like so:
[quoted text clipped - 21 lines]
> >
> > Can anyone help?

Reply to this Message