Jump to content

Excel cross-checking (what do I call it?)


-Demosthenes-

Recommended Posts

Okay, in my test I have two collums of city names and I used the third collum to find out if they had the same city names. In C1 I put "=VLOOKUP(B1, A1:A253,TRUE)" and it displayed the city name when B1 had a city contained between A1 and A253, and when it didn't it displayed "#N/A" (deleted the last letter so it was different. But when when I misspelled "Cedar City" as "Ceder City" it gave the value "Cedar Hills". And When I tried just "Cedar" it gave me "Castle Valley." So I'm not sure I'm doing it right, I have very little experience in excel.

 

I'm not sure what the second part of your post is saying, again, I'm an excel noob.

Link to comment
Share on other sites

You have two lists right? Do they have (or should they have) multiple entries for the same cities?

 

If not, you can sort the lists alphabetically, paste one next to the other, and visually compare them.

 

If one or both have non-unique entries in them, go to help and learn about pivot tables. They are one of Excel's best features, and massively overlooked by casual users.

 

I have never actually used VLOOKUP because Excel is satan's bumhole, but I am sure someone here will have done.

Link to comment
Share on other sites

One list is a complete list of all the cities' date=' the reference. The other is a list of cities (listed by zip code) that [i']should[/i] contain all the cities. I'm trying to make sure it does.

 

I'll look up pivot tables.

 

Ok, when you use TRUE, this means that you want the closest answer. If you use FALSE, it will only return the exact answer. Most people will tell you to sort both columns ascending. This is required for TRUE, but not for FALSE.

 

Also, I don't see your return column index value. Try this:

 

VLOOKUP(B1,A:A,1,FALSE)

 

This says, do a vertical lookup on the value in cell B1 in the entire column of A. If a match is found, then return that value, if not, then return #N/A.

 

#N/A is telling you it couldn't find that value, so that would be the same as saying the city name is not in the list, or it's mispelled.

 

I don't know of any way you could design a function to find mispelled city names.

Link to comment
Share on other sites

Sort numbers in column A, then past following formula in B column, except the first cell, which you would set to 1.

 

=IF(A2=A1,B1+1,1) this will increment if repeated or set to 1 if not.

 

If you are doing alot of this kind of stuff, importing to Access and doing queries would be some useful knowledge. Excel is great for calculations, statistics and quick sorting. Access is better with sorting, counting, etc

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.