Populate column for list of towns
Hello Guys
I am new and have just registered, I have been trying to solve a problem for over a week now in excel.
I have a list of towns, region
The list is over 1500 records for each
I was wondering if there is a way I can fill in the region and country columns automatically -i.e. through a script or function or macro based on what is entered in the town row/ field
e.g
Town= Cheltenham
if the town is Cheltenham, I would like the corresponding region column to pollute with glouctershire
I have tried using functions such as:
=IF(A3="CHELTENHAM","Glouctershire",IF(A3="LONDON","LONDON",IF(A3="Leeds","Yorkshire",A9)))
but as I have over 1500 towns, the error message comes up in excel stating the formula is too long- I think it is limited to 255 characters or so
I would appreciate any help as it is urgent.
Thanks to all in advance[/SIZE][/SIZE]
Last edited by kingjasonwill; 12-10-2009 at 09:52 PM. Reason: Need to change title
Thanks for changing the title and the font.
You'll need to set up a lookup table like this:
If you put this table in columns x and y, starting in row 1, then you can use VLOOKUP() instead of nested IFsCode:city region Cheltenham Glouctershire London London Leeds Yorkshire etc.
=vlookup(A3,$x$1:$y$100,2,false)
adjust cell addresses to suit
hth
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Hi
Sorry I am new to using excel, can you please explain to me what the line:
=vlookup(A3,$x$1:$y$100,2,false)
means and where I change the values?
Say Cheltenham is in A2 and I want Glouctershire is in B2
So If I input cheltenham anywherein the A column, it will update corresponding B column with Gloucestershire
and the same for London in A3
Leeds in A4
Thanks, your assistance will be appreciated
Hi kingjasonwill,
If you have the lookup table set up as mentioned in the previous post, then you put that formula in the place you want it to show the region.
So if A2 is Cheltenham then you put the Vlookup formula in B2. The formula then scans the list for Cheltenham, and returns the corresponding entry from column 2.
Are the locations you need to place regions aganst unique or duplicated throughout the record sets?
Hi
thanks for the reply.
Yes they are duplicated throughout.
e.g. a list such as:
london
cheltenham
london
london
cheltenham
leeds
leeds
cheltenham
glasgow
glasgow
I have tried the formula and it works but it brings up a #N/A message if the names are duplicated?
Can you help or guide me as to what I am doing wrong please?
Your lookup table should include each name and area only once. Also if you are populating the formula with autofill what your range, as it will move it down automatically.
So if your lookup table has london on top, and your initial range was A1:B2 in the formula and you autofill, range in the second formula will be A2:B3, so you need to make the range an absolute reference so that it still sees London at the top.
Cheers,
Justin
OK, Thanks, But I am a little confused, sorry.
I have included the file I am working on, I have simply created votes data.
Can you please check it and see what is wrong please?
Hi,
You're almost there! Just one thing: you're using relative references for the lookup range, so the references will change when the formula is copied down. Instead of
=VLOOKUP(C2,Party!A1:B10,2,FALSE)
Use
=VLOOKUP(C2,Party!$A$1:$B$10,2,FALSE)
With the $sign, the cell addresses are "absolute" and won't be changed when the formula is copied down. You want the C2 to be adjusted to C3 in the next row etc, but you always want to look up the same table, so you must make sure the lookup table is referenced with absolute references.
Hope that makes it clearer.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Yes, that makes it a million times clearer, thanks million for your help
Hi
Just one more thing, I have tried the solution, I was wondering can you please tell me how to ensure if my lookup value is not in the lookup list, i.e. no corresponding region, the field doesn't display #N/A and can just be blank?
Thanks in advance
Hi
Also
I would like to save the excel document as a CSV file to import into SQL.
How can I clean up the worksheet to show only the values from the formulas not the actual formula?
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hi
Thanks
Here is an attachment of the file I am working on
At the moment, 78 does not match any corresponding values in the lookup table. This may be the case when I polute all data. So I would like to know how to make sure the #N/A does not show up in D7 on the "voters" worksheet.
Also if I was to save the file as a CSV file to import into my phpmyadmin, how do I clean up table so it can show final values only in voters worksheet so I can delete the "Party Codes" worksheet.
Many thanks well in advance
Can you help please?
hi
when i say I would like it to not show up the #N/A value, I mean is it possible for it to remain blank if there is no value to look up in the "Party Codes" table
Thanks
Can you help please?
Hi,
the easiest, though not the most elegant is
=IF(ISNA(<the vlookupformula>),"",<the vlookupformula>)
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks