Q: Using lookups to normalize a database?

[Charles W. Stricklin]

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

 

[Guest]

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]

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