Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Q: Using lookups to normalize a database?
This new be a n00bie question, but I'd still appreciate any help you
people deign to provide. I'm trying to normalize a database, At current, all the data is in one big Excel spreadsheet. In one worksheet, I have 83 row/records containing data from 54 unique cities/locations. In another worksheet I have all the names of cities and towns from a 5 state area (over 3,000), alphabetized and given an ID number. Is there a relatively painless way to assign the correct ID number from the 2nd worksheet in the appropriate column in the 1st worksheet? In other words, replace every instance of Abbeville, Louisiana to 1, Abbeville, Mississippi to 2, Abbott, TX to 3, etc. I would imagine this would be a jump for lookups, but I've never used them so I can;t be sure. Any help is much appreciated. Charles |
#2
|
|||
|
|||
Hi Charles
You can use a VLOOKUP formula to add the ID into a new column on your first sheet. See here for more help on vlookups: http://www.contextures.com/xlFunctions02.html Your second sheet will be the lookup table (called sheet2 in my example). Assuming you have the cities in column A in this sheet and the IDs in column B then in a new column on your original sheet (which I am assuming has the citie in column A) you could use the formula: =VLOOKUP(A2,Sheet2!$A:$B,2,0) Enter this in row 2 and copy down. Hope this helps Rowan "Charles W. Stricklin" wrote: This new be a n00bie question, but I'd still appreciate any help you people deign to provide. I'm trying to normalize a database, At current, all the data is in one big Excel spreadsheet. In one worksheet, I have 83 row/records containing data from 54 unique cities/locations. In another worksheet I have all the names of cities and towns from a 5 state area (over 3,000), alphabetized and given an ID number. Is there a relatively painless way to assign the correct ID number from the 2nd worksheet in the appropriate column in the 1st worksheet? In other words, replace every instance of Abbeville, Louisiana to 1, Abbeville, Mississippi to 2, Abbott, TX to 3, etc. I would imagine this would be a jump for lookups, but I've never used them so I can;t be sure. Any help is much appreciated. Charles |
#3
|
|||
|
|||
Rowan wrote:
Hi Charles You can use a VLOOKUP formula to add the ID into a new column on your first sheet. See here for more help on vlookups: http://www.contextures.com/xlFunctions02.html Your second sheet will be the lookup table (called sheet2 in my example). Assuming you have the cities in column A in this sheet and the IDs in column B then in a new column on your original sheet (which I am assuming has the citie in column A) you could use the formula: =VLOOKUP(A2,Sheet2!$A:$B,2,0) Enter this in row 2 and copy down. Hope this helps Rowan Rowan, After a little fiddling around I got it to work, and it works perfectly. Thanks Charles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query a database on a server not working | Excel Discussion (Misc queries) | |||
Links to External Database | Excel Discussion (Misc queries) | |||
make existing pivot charts point at the same database | Charts and Charting in Excel | |||
The Template Wizard retains the original location for my database | Excel Discussion (Misc queries) | |||
Graphing Database Growth Rate | Charts and Charting in Excel |