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
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
