Create an additional column in tab Data that shows state from tab State (Explain procedure)
How do I I have to solve the question using this excel data set but note that this is not the full data set. I just need the steps please.
date_sign_up | customer_id | product_name | marketing_channel | City |
4/17/2015 13:11 | 71041 | Classic - 3 meals per week for 2 people | Search Engine Marketing | San Diego |
4/14/2015 11:32 | 103289 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Los Angeles |
4/14/2015 9:53 | 107746 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Los Angeles |
4/14/2015 16:08 | 157441 | Classic - 3 meals per week for 4 people | Newsletters (internal) | San Francisco |
4/18/2015 8:04 | 158646 | Classic - 3 meals per week for 2 people | Newsletters (internal) | San Diego |
4/18/2015 15:41 | 178843 | 3 Meals (vegetarian) for 2 people | TV / Radio Advertising | Miami |
4/15/2015 9:45 | 179297 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Philadelphia |
4/15/2015 8:21 | 182439 | 3 Meals (vegetarian) for 2 people | Newsletters (internal) | San Francisco |
4/17/2015 6:57 | 186180 | Classic - 3 meals per week for 2 people | Partnership Marketing | San Diego |
4/19/2015 20:39 | 190396 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | Miami |
4/14/2015 10:26 | 194229 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Los Angeles |
4/13/2015 20:17 | 194353 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Washington |
4/15/2015 14:43 | 200286 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Philadelphia |
4/16/2015 7:35 | 201307 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | 1San Diego |
4/15/2015 6:54 | 203319 | Classic - 3 meals per week for 2 people | Newsletters (internal) | San Francisco |
4/14/2015 8:35 | 205233 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | Los Angeles |
4/13/2015 12:08 | 205322 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Chicago |
4/16/2015 5:04 | 205611 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | San Diego |
4/15/2015 12:17 | 208066 | 3 Meals (vegetarian) for 4 people | Newsletters (internal) | Philadelphia |
Answer: I am giving you the steps to do in excel.
First of all, Create a Master table of state and city names where against each city their state names are mentioned.
Now insert a column in this data set in excel and make the column name as the state
Now use the Vlookup function =Vlookup(select the cell containing the city name, select the entire data range in the master table where the city name and state are listed,2, False)
The sampling formula would be like: (suppose the city name is in column E of current sheet)
State_name=Vlookup(E2,Satesheet!$A$1:$B$10,2,False)
Now drag down or copy-paste the formula in the entire column, you will get the state name against each entry or row where the cities are mentioned.
Get Answers For Free
Most questions answered within 1 hours.