Delete Part of Cell, Perform Lookup

harmanie

New Member
Joined
Sep 7, 2005
Messages
6
I have a spreadsheet with 4 columns, Column A is the city and state pulled from a data export. column two is the number of records for that state, column 3 is the city only and column 4 is the state only. Columns 3 and 4 are created using the text to column function via VBA when the sheet is opened. The data entry for this data export is very poor. Sometimes the state is left off or they type too many characters so it is cut off to only 1 letter instead of 2. In many cases they enter an office name in parenthesis after the city. (Example, Boston (Acton), MA)

For the rows in column 4 that have a missing state, or only one letter of the state, I need the extra info in the city field stripped out ((Action)) and I need to use a lookup function to find that city on a table (On the same sheet, Columns K:L1000) and put the correct state in the state column. If there are multiple cities for that state, or the city isn't found because they mis-spelled it, I need it to prompt the user to select one by displaying column 1 (example Boston (Acton),MA) and asking which state it belongs to. That is actually a bad example because that one has a state, this would only apply to the records missing it so it would display something more like Boston(Acton).

To summarize, I have a column with a user entered city and state. The end product I am trying to get is simply an accurate city and an accurate state. There is extra data added into the field (office names in parenthesis) and sometimes the state is missing or cut off. I have a complete list of all cities and states in the us (this is my lookup table). The only way I can see to populate the state if the state is missing or city is spelled wrong is to look it up on the table or to ask the user. If there is a better way to get to this same end product, please share!

The list of cities is pulled from a data import and compiled with VBA from the raw data so it will be a different list each time the sheet is opened and scripts executed.

Thanks in advance for any assistance on this task!
Christine

Sample Data
Hauppauge (Long Island), N 129 Hauppauge (Long Island) N
Houston, TX 3 Houston TX
Indianapolis, IN 18 Indianapolis IN
Kansas City (Overland Park 2 Kansas City (Overland Park
Lake Oswego (Northwest Reg 33 Lake Oswego (Northwest Reg
Madison, WI 120 Madison WI
Melville (Long Island) 4 Melville (Long Island)
Melville (Long Island), NY 3 Melville (Long Island) NY
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Can you set the user input to select from dropdown lists or whatever ?
 

harmanie

New Member
Joined
Sep 7, 2005
Messages
6
Yes, a dropdown list would be fine, in fact ideal in my opinion, less chance for data entry errors!
Christine
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top